PDA

View Full Version : Solved: Error Message On Protected Worksheet



zoom38
03-15-2006, 05:42 PM
My worksheet works fine but when I protect the sheet I get the following error message: "Run-Time Error '1004':", "Unable to set the ColorIndex property of the interior class". I am assuming I have the sub in the wrong spot but I really don't know. Anyone know why Im getting this error message and how to solve it?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range("B8:O34")) Is Nothing Then
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35

Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34

Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36

Case "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
End Sub

When I click on Debug it highlights "Target.Interior.ColorIndex = -4142" on the Case Else line.

Thanks
Gary

Jacob Hilderbrand
03-15-2006, 06:22 PM
Since the sheet is protected you cannot just change the fill property but...

Put this as the first line of your Sub.


Sh.Protect Password:="Pass", UserInterfaceOnly:=True

Just chage "Pass" to your actual password.

zoom38
03-15-2006, 08:43 PM
I do not understand why that worked but it worked great. Thanks for the help DRJ.


Gary

smc2911
03-15-2006, 09:48 PM
I worked because when your worksheet is protected (and the protection is set to apply to formatting), even macros cannot change protected shells. The UserInterfaceOnly command changes to protection so that it doesn't apply to changes made using macros, so your code then works. Note that while it may be slight overkill to change the setting with every call to Worksheet_Change, it is convenient because if you save & close your workbook, the UserInterfaceOnly will be forgotten. The suggested approach ensures that it is always set. Note also that, depending on the reason you have chosen for applying protection, you will probably want to password protect your VBA (Tools -> VBA Project Properties -> Protection) otherwise someone can easily look at the code to discover the password.

Sean.

zoom38
03-16-2006, 08:14 AM
Thanks for explaining TheUserInterface command Sean, greatly appreciated.

Gary