VBA - Popup message when user selects a Protected Cell

Hi All,

I searched on the forum and found a previous post that matched my situation.

I'm trying to created a MsgBox with a message when a user tries to edit a protected cell in a workbook (I don't want the default message that Excel produces when you edit a protected cell).

I've used the following code from a previous post, but can't seem to get it to work:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim vUnlockedCell As Variant
    Dim rUnlockedCells As Range

'   Specify the address of the first user-editable cell here
    Set rUnlockedCells = Me.Range("AO5")

'   Specify the addresses of the remaining user-editable cell here
    For Each vUnlockedCell In Array("AO7:AR260")
        Set rUnlockedCells = Union(rUnlockedCells, Me.Range(vUnlockedCell))
    Next vUnlockedCell

    If Union(Target, rUnlockedCells).Address <> rUnlockedCells.Address Then
        MsgBox "This selection may not be edited", vbExclamation, "Invalid selection"
    End If

End Sub

Anyone any suggestions?

Apologies all - by some strange wonder - I've gone into my spreadsheet this morning and it's working just fine

Modified it slightly - specifying the locked cell range as opposed to the user-editable range - and also adding in an action to Undo and put the formula back:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim vLockedCell As Variant
    Dim rLockedCells As Range

'   Specify the address of the first locked cell
    Set rLockedCells = Me.Range("E6")

'   Specify the addresses of the reamining locked cells here
    For Each vLockedCell In Array("E7:AI260")
        Set rLockedCells = Union(rLockedCells, Me.Range(vLockedCell))
    Next vLockedCell

    If Union(Target, rLockedCells).Address = rLockedCells.Address Then
        MsgBox "Do not modify this cell. Please change the shift on the ""Rota"" tab!", vbExclamation, "Invalid selection"
        
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    
    End If

End Sub

Scott - I have placed this in the Sheet code ad not in a module. I have code in the same sheet for button click actions so this is the sheet code isn't it?

coreytroy - I specifically want a user-defined message as opposed to just protecting the worksheet and having the default Excel pop-up.