Thread: VBA Display message when sheet protected

Hi.

Hoping someone can help as this is driving me crazy as im still pretty much a beginner when it comes to vba!

I have a sheet which is protected other than a copy of cells which may be edited. I want to display a message box when the user clicks elsewhere on the sheet. I have the message box in a macro, and i have the sheet protected - but can not seem to join the two together so the box displays automatically.

Can anyone help?! Thank you!

Hi there,

Try entering the following code in the VBA module of the worksheet for which you want the warning message to be generated (right-click on the worksheet tab, and select View Code)

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("A5")

'   Specify the addresses of the remaining user-editable cell here
    For Each vUnlockedCell In Array("A8", "B3:B7", "C11:F15")
        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

selection (see note below)

Hope this helps - please let me know how you get on.

Regards,

Greg M


NOTE TO FORUM ADMINISTRATORS

When I enter the word "selection" without highlighting the "t" in the above code, the screen displays the "The website cannot display this page" error. It took me quite a while to figure out exactly which part of my post was causing the error!

Any comments?

Greg M

This is great Greg - thank you! Works perfectly - exactly what i was looking for!

Hi Nic,

Many thanks for your feedback - I'm glad I was able to help.

Regards,

Greg M


SHG - many thanks for your prompt reply to my question.