can't get vba to change shading when protected

[Matt]

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("A140").Interior.ColorIndex = xlNon
.PrintOu
.Range("A5:B5,C69,O10:O11,M10:M11,K10:K11,I10:I11,G10:G11,E10:E11,A10:C11,C1212,O16,M16,K16,I16,G16,E16,A16:C16,A1733,O3440,A34:B40").Interior.ColorIndex = 2
End Wit
Application.EnableEvents = Tru
Application.ScreenUpdating = Tru
End I
End Sub

 

[Bob Phillips]

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)

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("A140").Interior.ColorIndex = xlNone
.PrintOut

..Range("A5:B5,C69,O10:O11,M10:M11,K10:K11,I10:I11,G10:G11,E10:E11,A10:C11,
C1212,O16,M16,K16,I16,G16,E16,A16:C16,A1733,O3440,A34:B40").Interior.C
olorIndex = 24

 

[george]

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("A140").Interior.ColorIndex = xlNone
.PrintOut
.Range

("A5:B5,C69,O10:O11,M10:M11,K10:K11,I10:I11,G10:G11,E10:E
11,A10:C11,C1212,O16,M16,K16,I16,G16,E16,A16:C16,A1733,
O3440,A34:B40").Interior.ColorIndex = 24