Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with sheet unprotect
Hi all,
I'm having trouble with Excel sheet protection. I'm programming a macro that will run through an Excel Workbook, searching a every sheet for certain text and then shading the first 31 columns of the row in which the text is found. The sheet is protected, but the code (below) tries to unprotect before it runs. The unprotect method seems to succeed (no errors are countered), but when it tries to change the ColorIndex property it fails as the sheet is still protected. In this code, each sheet is named as the date in the format "mmm yyyy", and the code only runs on the sheet that is equal to the variable StartMonth. I've commented in the code below where the error is encountered. Any help would be greatly appreciated. Thanks, Wayne -------------------------------------------- For C = 1 To Sheets.Count Sheets(C).Activate ActiveSheet.Unprotect ("PASSWORD") For D = 1 To 74 ' Only checks the first 74 rows after Range("A4") If Range("A4").Offset(D, 0).Value = "TEXTTOCHECKFOR" Then For E = 1 To 31 ' Shade and enter 'X' in the first 31 columns Range("A4").Offset(D, E).Interior.ColorIndex = 12632256 ' THE LINE ABOVE IS WHERE IT CRASHES Range("A4").Offset(D, E).Value = "X" Next E Exit For ' Stop checking for "TEXTTOCHECKFOR" once it's been found End If Next D ActiveSheet.Protect ("PASSWORD") Next C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with sheet unprotect
Hi
Your problem is not with sheet protect but the value of the ColorIndex, this refers to the palette color which has only 56 values. Values above 56 will throw an error. Range("A4").Offset(D, E).Interior.ColorIndex = 5 'e.g. - set it blue If you want to specify a specific color you could use the RGB function, values for each color RGB can be 0 to 255 e.g. Range("A4").Offset(D, E).Interior.Color = RGB(0, 0, 255) ' e.g. set it blue -- Cheers Nigel "Wayne" wrote in message ... Hi all, I'm having trouble with Excel sheet protection. I'm programming a macro that will run through an Excel Workbook, searching a every sheet for certain text and then shading the first 31 columns of the row in which the text is found. The sheet is protected, but the code (below) tries to unprotect before it runs. The unprotect method seems to succeed (no errors are countered), but when it tries to change the ColorIndex property it fails as the sheet is still protected. In this code, each sheet is named as the date in the format "mmm yyyy", and the code only runs on the sheet that is equal to the variable StartMonth. I've commented in the code below where the error is encountered. Any help would be greatly appreciated. Thanks, Wayne -------------------------------------------- For C = 1 To Sheets.Count Sheets(C).Activate ActiveSheet.Unprotect ("PASSWORD") For D = 1 To 74 ' Only checks the first 74 rows after Range("A4") If Range("A4").Offset(D, 0).Value = "TEXTTOCHECKFOR" Then For E = 1 To 31 ' Shade and enter 'X' in the first 31 columns Range("A4").Offset(D, E).Interior.ColorIndex = 12632256 ' THE LINE ABOVE IS WHERE IT CRASHES Range("A4").Offset(D, E).Value = "X" Next E Exit For ' Stop checking for "TEXTTOCHECKFOR" once it's been found End If Next D ActiveSheet.Protect ("PASSWORD") Next C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to unprotect sheet so I cn sve it but need a password? | Excel Discussion (Misc queries) | |||
unprotect sheet macro | Excel Discussion (Misc queries) | |||
how to Unprotect sheet | Excel Discussion (Misc queries) | |||
Excel VBA - Problems using Protect/Unprotect a worksheet | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming |