Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't get vba to change shading when protected
I have code that changes the cell shading in a worksheet, however, when I protect the worksheet, I get an error in any code that changes the shading in cells. I'm not sure if I need to add some code so that it will allow the changes when protected or if I need to add code to unprotect it then place protection on it again. Any help would be greatly appreciated. Here is an example of the code that is causing problems. Thanks. Mat
Private Sub Workbook_BeforePrint(Cancel As Boolean If ActiveSheet.Name = "TIME AND LEAVE" The Cancel = Tru Application.EnableEvents = Fals Application.ScreenUpdating = Fals With ActiveShee .Range("A1:P40").Interior.ColorIndex = xlNon .PrintOu .Range("A5:B5,C6:P9,O10:O11,M10:M11,K10:K11,I10:I1 1,G10:G11,E10:E11,A10:C11,C12:P12,O16,M16,K16,I16, G16,E16,A16:C16,A17:P33,O34:P40,A34:B40").Interior .ColorIndex = 2 End Wit Application.EnableEvents = Tru Application.ScreenUpdating = Tru End I End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't get vba to change shading when protected
Matt,
Do the latter, unprotect the worksheet before printing, then protect it afterwards, -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Matt" wrote in message ... I have code that changes the cell shading in a worksheet, however, when I protect the worksheet, I get an error in any code that changes the shading in cells. I'm not sure if I need to add some code so that it will allow the changes when protected or if I need to add code to unprotect it then place protection on it again. Any help would be greatly appreciated. Here is an example of the code that is causing problems. Thanks. Matt Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "TIME AND LEAVE" Then Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet .Range("A1:P40").Interior.ColorIndex = xlNone .PrintOut ..Range("A5:B5,C6:P9,O10:O11,M10:M11,K10:K11,I10:I 11,G10:G11,E10:E11,A10:C11, C12:P12,O16,M16,K16,I16,G16,E16,A16:C16,A17:P33,O3 4:P40,A34:B40").Interior.C olorIndex = 24 End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't get vba to change shading when protected
You need to protect the sheet via vba and set the
UserInterfaceOnly to true which will allow vba to make changes eg Worksheets("Sheet1").Protect UserInterfaceOnly:= True The other way to do it is to unprotect the sheet, execute the code and then re-protect it again. eg Worksheets("Sheet2").Unprotect execute code Worksheets("Sheet2").Protect -----Original Message----- I have code that changes the cell shading in a worksheet, however, when I protect the worksheet, I get an error in any code that changes the shading in cells. I'm not sure if I need to add some code so that it will allow the changes when protected or if I need to add code to unprotect it then place protection on it again. Any help would be greatly appreciated. Here is an example of the code that is causing problems. Thanks. Matt Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "TIME AND LEAVE" Then Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet .Range("A1:P40").Interior.ColorIndex = xlNone .PrintOut .Range ("A5:B5,C6:P9,O10:O11,M10:M11,K10:K11,I10:I11,G10: G11,E10:E 11,A10:C11,C12:P12,O16,M16,K16,I16,G16,E16,A16:C16 ,A17:P33, O34:P40,A34:B40").Interior.ColorIndex = 24 End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change selected cell shading colour | Excel Discussion (Misc queries) | |||
shading based on change of values | Excel Discussion (Misc queries) | |||
Shading cells whenever they change Months | Excel Discussion (Misc queries) | |||
How can I activate cell shading in a protected worksheet? | Excel Discussion (Misc queries) | |||
Is there a way to change the default shading color ? | Excel Discussion (Misc queries) |