Changing Cell Colour in Protected Sheet

[Coope]

Hi,

I'm trying to protect some cells on a spreadsheet from having their
formulae deleted accidently etc. The only problem is I run a VBA
program ontop of the spreadsheet that consists of a load of forms. The
inputs into these forms add data to the cells on the spreadsheet and
it all works well until I protect the sheet.

The reason being is that I use some VB code to change the colour of a
cell. The worksheet is protected but the cell in question is not
locked but the code fails. Why can my VBA code not change the colour
of an unlocked cell in a protected sheet? It lets me add or change
data, so why won't it let me change the format?

I'm using Excel 2000.

I've read about adding an "unprotect sheet" code before my colour
change code and then a "protect sheet" code after but that seems
rediculous. Surely there is an option in Excel that prevents random
users from messing up cell contents but still allows VB code to do
what it needs to do?

Any help apprieciated.

Craig

 

[Gord Dibben]

Later versions allow cell formatting when sheet is protected.

I believe this was introduced in 2002 version.

You must use the "ridiculous" method.


Gord Dibben MS Excel MVP