Macro error with protected sheet, debug flagging color

[phownz]

Hi all,

I'm having an issue whereby when protecting a sheet my macro (responsible for highlighting a cell with a colour when a word limit is exceeded at 200, 350 and 500, each within a separate column) results in a "Run-time-error 1004"

The debug highlights this section of the code:

The full code is here:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column >= 24 And Target.Column <= 26 Then
        Dim txt As String
        Dim lim As Long
        
        txt = ""
        
        Select Case Target.Column
        Case 24
            lim = 200
            If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
                                
            End If
        
        Case 25
            lim = 350
            If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
            End If
        
        Case 26
            lim = 500
             If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
            End If
        End Select
        
        If txt <> "" Then MsgBox "Cell exceeds character limit of " & CStr(lim) & vbCrLf & txt
    End If
End Sub

The macro works without any issues when the sheet is unprotected, the cells the macro is responsible for are UNPROTECTED as data will be copy and pasted in.

I've read some posts re: allowing the VBA code to unprotect the sheet before running the macro but this seemed to be related to locked cells.

Do I have to apply the same sort of logic to my code? And if so what would I need to add?

Thanks in advance.

 

[GWteB]

You could either start and end your procedure with

    ' start
    Target.Parent.Unprotect Password:="YoUrPaSsWoRd"
    '
    ' your code to do some stuff
    '
    ' end
    Target.Parent.Protect Password:="YoUrPaSsWoRd"

or you could protect your worksheet only for change attempts through the user interface, which you only have to do once.:

    ThisWorkbook.Sheet("Sheet1").Protect Password:="YoUrPaSsWoRd", UserInterfaceOnly:=True

 

[phownz]

Hi GWteb,

Thanks for the quick reply. I've tried the first option and that works well.

I'm struggling a bit with the second, probably where I'm placing it as I get a compile error. Where would that need to be called in the macro?

Thanks

 

[GWteB]

The second one needs just to be invoked once. It errors on already protected sheets. So unprotect the worksheet involved, invoke this code once and save your workbook. Done!
From now on users can't change anything while macros can.

 

[phownz]

Perfect! My worksheet was protected beforehand... Both work, and many thanks!

 

[GWteB]

You're welcome and thanks for the follow-up