Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on a protected sheet
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 -- J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on a protected sheet
Hi James,
Try entering this first line beneath sub, ActiveSheet.Protect UserInterfaceOnly:=True Skinman "James" wrote in message ... 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 -- J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on a protected sheet
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. -- J "Skinman" wrote: Hi James, Try entering this first line beneath sub, ActiveSheet.Protect UserInterfaceOnly:=True Skinman "James" wrote in message ... 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 -- J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on a protected sheet
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. "James" wrote in message ... 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. -- J "Skinman" wrote: Hi James, Try entering this first line beneath sub, ActiveSheet.Protect UserInterfaceOnly:=True Skinman "James" wrote in message ... 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 -- J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting & Protected Ranges disappear after running macro | Excel Discussion (Misc queries) | |||
Macro on Protected sheet | Excel Programming | |||
If I have my sheets protected will that prevent me running a macro to hide certain sells? | Excel Worksheet Functions | |||
Error 1004 when running on a protected sheet | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |