I use Excel 2007 (Swedish version)
If the value in cell D5 is "TCLE", then some command buttons are supposed to be invisible and their cell color should be (255,255,255).
If the value in cell D5 is not "TCLE", then some command buttons are supposed to be visible and their cell color should be (223,223,223).
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("MED styrning").Range("Q8") > 200 Then
CommandButton5.Visible = False
Else
CommandButton5.Visible = True
End If
If Worksheets("MED styrning").Range("D5").Value = ("TCLE") Then
CommandButton12.Visible = False
Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
Else
CommandButton12.Visible = True
Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
End If
If Worksheets("MED styrning").Range("D19") = ("") Then
CommandButton1.Visible = False
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
Else
CommandButton1.Visible = True
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)
End If
End Sub
Display More
It works perfectly well, until I protect it with:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Worksheets("MED styrning")
.EnableSelection = xlUnlockedCells
.Protect Contents:=True, UserInterfaceOnly:=True
End With
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub
Display More
Then I get an error code '1004'
and it points at one of these lines:
Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)
If I comment out the line, it points at one of the other lines instead.
If I comment all four lines out, the error messages stop showing up, but then I don't have the desired functionality.
I have tested to add
in the beginning of the code section.
And then:
in the end of the code section.
It caused the request of filling in the password several times, so it didn't help at all.
But, why does it complain about the change of color in the cell, just because the sheet is protected ???
I still have to find a workaround.
I also have asked the same in the MrExcel.com forum
/ Hans Gatu