Excel VBA Error On Unlocked Cells When Sheet is Protected

Question

I have a large database of contacts and would like to lock and protect the header rows (1:5). The rest remain unlocked so that the user can edit them as they wish.

When I Protect the Worksheet, I'm allowing the users to only:

• Select unlocked cells
• Format columns (widen as they need to display entered info)
• Insert rows
• Delete rows
• Sort

I don't want them to be able to insert columns as it messes with the macros. They can only insert rows that are below the header.

All of the existing macros work when protected except one. Using a double left-click of the cell next to the contacts name places a check mark or 'X' and conditionally highlights their information accordingly as having been selected or not selected:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
    If Intersect(Target, Range("SelectionMaster")) Is Nothing Then Exit Sub
    'set Target font tp "marlett"
    Target.Font.Name = "marlett"
    'Check value of target
    If Target.Value < "a" Then
        Target.Value = "a" 'Sets target Value = "a"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.Value = "r"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "r" Then
        Target.ClearContents 'Sets Target Value = ""
        Cancel = True
        Exit Sub
    End If
    End Sub

The first time it's used after protecting, it brings up an error on Target.Font.Name = "marlett" and I don't understand why. After the initial error, double clicking only lets you edit the cell.

I appreciate the help!

question from:https://stackoverflow.com/questions/66053358/excel-vba-error-on-unlocked-cells-when-sheet-is-protected

Answer 1

Waitting for answers