Setting ColorIndex On Protected Sheet

You have to un-protect the sheet, run your code and then protect the sheet again. Try this.

'unprotects the active sheet
ActiveSheet.Unprotect

'your code here

'protect the sheet again
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True

I hope it helps. -D

Thanks D-Alan!

Unfortunately, when the macro reaches the
ActiveSheet.Unprotect command, it requests the password for the sheet; something the user won't be privvy to.

One option, I imagine, is to to display a message telling the user what the password is for unprotecting the sheet; but then that kind of defeats the purpose of protecting the sheet!

Are there any other methods for doing this?

M

If your decision to put the 'complete' in the cell and highlight it is based on information in other cells, use an appropriate formula in that cell and add the necessary conditonal formatting.

On 2002-08-09 15:13, markkramer wrote:
Thanks D-Alan!

Unfortunately, when the macro reaches the
ActiveSheet.Unprotect command, it requests the password for the sheet; something the user won't be privvy to.

One option, I imagine, is to to display a message telling the user what the password is for unprotecting the sheet; but then that kind of defeats the purpose of protecting the sheet!

Are there any other methods for doing this?

M

Click to expand...

Hi M
If you Don't wish to proceed with Tusharms suggestion
and do it via code then you need to supply the password within your code.
Lookup Protect method within the VBA Editor help, it will give you the required syntax and method.

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails

This message was edited by Ivan F Moala on 2002-08-09 17:56

Thanks everyone!

I decided to display a message to the user telling them what password to enter.

While I still want to protect this sheet, if the user knows the password to it, they can't create too much damage.

The sheet essentially consists of a series of buttons. Each button is linked to a macro. The "complete" appears to the right of each button to indicate that the user has already clicked on that button and ran that macro.

Thanks, again, for your help! I've included the final code below.

M


' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ''' Mark the button complete
' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("Instructions & Index").Select
Range("K19").Select
MsgBox "If asked to ener a password to unprotect the sheet, enter: password"
ActiveSheet.Unprotect
ActiveCell.FormulaR1C1 = "Complete"
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
Range("A1").Select

On 2002-08-12 07:32, markkramer wrote:
{snip}
I decided to display a message to the user telling them what password to enter.
{snip}

Click to expand...

You are joking, right? If you are willing to share the password with anyone who clicks on a button, why bother protecting the sheet?

Nope! Not joking! Pretty surprising, huh? It took me a lot of consideration to come to this conclusion, but I could not come to a better solution.

I know my user community very well and I know that they are not very sophisticated on Excel. They'll enter in the password when asked but will probably not realize that they can unlock the sheet at a later time using the same password. Even if they did realize that they could unlock the sheet at a later time, I'd be very surprised if any of them do unlock the sheet--let alone know how to--after knowing the password. The reason for locking the sheet the rest of the time is so that they do not click on and change other areas of the worksheet.

This seemed to be the best solution to accomplish what I was trying to do. I'm open to additional suggestions if you have them!

BTW: The rest of the workbook is protected using other passwords.