Sheet protection

[anamarie30]

I want to protect sheet because I have some formulas that I don't want people
hace access to it to modify. But I have some cells that people will enter
some values and they give a message and change the cell color depends of the
value that they enter. My problem is when I protect the sheet using Tools >
Protection > Protect Sheet... and when I write the value that I want in the
cell when sheet is protected I have an error. I obtain the message that I
want but when the cell will change the color I received and error message
like: "Run-time eoor '1004': Unable to set the ColorIndex property of the
Interior class". How can I resolve that problem. When the sheet is
unprotected my code working great but when it protected I recived this
message.

Thanks in advance.

 

[Tomasz Gasecki]

|I want to protect sheet because I have some formulas that I don't want
people
| hace access to it to modify. But I have some cells that people will enter
| some values and they give a message and change the cell color depends of
the
| value that they enter. My problem is when I protect the sheet using Tools| Protection > Protect Sheet... and when I write the value that I want in
the
| cell when sheet is protected I have an error. I obtain the message that I
| want but when the cell will change the color I received and error message
| like: "Run-time eoor '1004': Unable to set the ColorIndex property of the
| Interior class". How can I resolve that problem. When the sheet is
| unprotected my code working great but when it protected I recived this
| message.

What about to add a section to your macro to unprotect your sheet, then run
normally your code and then protect it again?

have a good day

Tomasz

 

[Jay]

Hi anamarie30 -

From your description, you are likely using a Worksheet_Change event
procedure to change the interior color of the cell. I agree with Tomasz that
a good solution would be to unprotect the worksheet in your code.

Insert the statements 'ActiveSheet.Unprotect' and 'ActiveSheet.Protect'
before and after the statement(s) that cause the run-time error.

If the worksheet is password protected the statements must include the
password, for example, ActiveSheet.Unprotect "anamariespassword" and
ActiveSheet.Protect "anamariespassword".