Macro error with sheet protection

[Wembley53]

Hi,

I have a worksheet that contains a button with an assigned macro to display some text. The macro (below) basically changes the font colour.

Sub ShowQ1()
'
' ShowQ1 Macro
'
Application.Goto Reference:="R5C3"
Selection.Font.ColorIndex = 0
End Sub

This works fine when none of the worksheet is protected. However if I protect the worksheet I get a runtime error 1004. Unable to set the ColorIndex property of teh font class.

The protection is not on the cells that the macro will affect.

Has anyone any ideas how I get around this?

Thanks

 

[VoG]

Try like this

Sub ShowQ1()
'
' ShowQ1 Macro
'
ActiveSheet.Unprotect Password:="zzz"
Application.Goto Reference:="R5C3"
Selection.Font.ColorIndex = 0
ActiveSheet.Protect Password:="zzz"
End Sub

 

[Michele]

This similar thing happened to me today.... But mine was a Macro that changed cell colors, so here is what I figured out by doing a SEARCH here on the topic

Private Sub Worksheet_Change(ByVal Target As Range)
    tRow = Target.Row
    tAddress = Target.AddressLocal(False, False)

[COLOR="Red"]ActiveSheet.Unprotect ("your password here")[/COLOR]    
    If Not Intersect(Target, Range("B" & tRow & ",H" & tRow & ",N" & tRow & ",U" & tRow)) Is Nothing Then
        If tAddress = "B" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 3
        If tAddress = "H" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 6
        If tAddress = "N" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 4
        If tAddress = "U" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = -4142
[COLOR="red"]ActiveSheet.Protect ("your password here")[/COLOR]    End If
End Sub

hope that turned out right, you see the code in RED is all I added to my existing code, works like a champ. good luck !!!

M

 

[Wembley53]

Just the ticket, it worked a treat. Thanks very much for your prompt replies.

Be lucky!