Runtime Error 1004

[mikemmb]

Hi,
I need help again, but I am learning a lot from you guys!

I have just applied protection to certain cells within a worksheet.
The required cells are protected and others are not.

However in some unprotected cells I have a sub to toggle "ticks" in the box see below:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B7:D1000")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub"

Whenever I click on one of these cells, the "ticks" do not toggle and I get the following error:
"Runtime error 1004
Unable to set the name property of the font class".

If I Unprotect the sheet it works fine.

Clearly the protection function is screwing up the font change in the sub, but I am stuck on why or how as I thought that I had strictly limited the protection to just specific cells. I have found some references to this error but no real conclusion as to the best solution?

Thanks,
Mike

 

[schielrn]

Can you just unprotect the sheet before changing the name and then protecting it again? Is it protedted with a password, if not try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B7:D1000")) Is Nothing Then
activesheet.unprotect
Target.Font.Name = "Marlett"
activesheet.protect
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub

Otherwise if it has a password you will have to put that after the protect in quotes.

Hope that helps.

 

[Mister H]

Hi Mike:

I think you just need to Add Unprotect and Protect to your code. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
 
ActiveSheet.Unprotect
'ActiveSheet.Unprotect "password" 'IF password protected use this line instead
 
If Not Intersect(Target, Range("B7:D1000")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
 
ActiveSheet.Protect
ActiveSheet.Protect "password" 'IF password protected use this line instead
 
 
End Sub

Good Luck,
Mark

 

[mikemmb]

Thanks all, it was cured by putting unprotect/protect in the sub as suggested.
Thanks and Regards,
Mike

 

[mikemmb]

Hi,
In use the Unprotect/Protect solution works perfectly technically, but it does cause problems because regardless of the protection status prior to running the sub, it always ends up applying protection.

Is there a way of making the "protect" code conditional on the original status.
Something along the lines of: if it was protected before then re-protect it, if not do not protect it.

Regards,
Mike

 

[VoG]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim IsProtected As Boolean
If Target.Cells.Count > 1 Then Exit Sub
IsProtected = Me.ProtectContents = True
If IsProtected Then Me.Unprotect
If Not Intersect(Target, Range("B7:D1000")) Is Nothing Then
    Target.Font.Name = "Marlett"
    If Target = vbNullString Then
        Target = "a"
    Else
        Target = vbNullString
    End If
End If
If IsProtected Then Me.Protect
End Sub

 

[mikemmb]

Thanks VoG,
Will not be able to try this for a couple of days, so will not be able to respond, but where does my password (used in Mister H's post of the thread) come into this? do I swap Me for "mypassword"?

Thanks,
Mike

 

[VoG]

No! Me refers to the sheet in which the code resides. Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim IsProtected As Boolean
If Target.Cells.Count > 1 Then Exit Sub
IsProtected = Me.ProtectContents = True
If IsProtected Then Me.Unprotect Password:="mypassword"
If Not Intersect(Target, Range("B7:D1000")) Is Nothing Then
    Target.Font.Name = "Marlett"
    If Target = vbNullString Then
        Target = "a"
    Else
        Target = vbNullString
    End If
End If
If IsProtected Then Me.Protect Password:="mypassword"
End Sub