Running macro on a protected sheet

[James]

I am trying to set up a macro that automatically changes the background
colour of cells based on a condition. I have found the required macro with
the subseuqent conditional formatting requirements, and I can't get it to run
on a protected worksheet. It works fine when I unprotect the sheet, although
there are a number of formulas that I don't want users modifying.

The code is as follows:

Option Explicit

Dim NextTime As Date
Sub RepeatOneSec()
ActiveWorkbook.Styles("normal").NumberFormat = _
ActiveWorkbook.Styles("normal").NumberFormat
NextTime = Now() + TimeSerial(0, 0, 1)
Application.OnTime NextTime, "RepeatOneSec"
End Sub

Sub EndProcess()
Application.OnTime NextTime, "RepeatOneSec", , False
End Sub

The area I have problems with is the ActiveWorkbook.Styles lines. I get the
message "unable to set the NumberFormat property of the Style class"

Does anyone have any suggestions as to how to fix this?

Thanks

 

[Skinman]

Hi James,
Try entering this first line beneath sub,
ActiveSheet.Protect UserInterfaceOnly:=True
Skinman

 

[James]

I had used that line of code before, although I think I had put it in the
wrong place!!

I have had to write a few more lines of code in other areas, but everything
works ok. Just quickly, can I add a password to the UserInterface line?

Thanks for your help.

 

[Skinman]

I don't know, however the sheet stays protected when the macro finishes.
If you put the password in the macro others could see it, so then you would
have to protect the macro with another password.
Sorry I can't be of more help. Start a new post and someone else may be able
to help you.
Cheers, Skinman.