Hi,

I would really appreciate any advice on this if anyone can help.

I have created some code for Worksheet_SelectionChange that returns a message (only when sheet is unprotected) when any cell in columns Q:P are selected.

It works ok, however one issue I have is when you scroll over these cells using arrow keys on key board the code runs which is annoying as users may want to find data in these columns that way. They would soon get fed up as the message keeps popping up on every single cell movement.

I would really like to find a way to only run the code when:
1) The sheet is unprotected (which I have resolved)
2) Only run code when cell is selected with mouse click.

I guess what I am asking for is something that does not exist i.e. BeforeLeftClick event but was hoping there was some sort of work around.

Code I am using below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveSheet.ProtectContents = True Then
        Exit Sub
        Else
        If Not Intersect(Range("Q2:Q1048576"), Target) Is Nothing Then MsgBox "'Disposal Week No.' is calculated from the 'Date of Physical Disposal' using a formula." _
        & vbNewLine & vbNewLine & "If you are manually adding a sample to a new blank row please click the 'Add Formula' button at top of this worksheet." _
        & " The 'Add Formula' button will add a formula to the 'Disposal Week No.' in the row you specify." _
        & vbNewLine & vbNewLine & "Alternately if you are editing an existing sample submitted using the form from the home page the formula will already be inserted.", vbCritical
    End If
    
    If Not Intersect(Range("R2:R1048576"), Target) Is Nothing Then MsgBox "'Disposal Month' is calculated from the 'Date of Physical Disposal' using a formula." _
    & vbNewLine & vbNewLine & "If you are manually adding a sample to a new blank row please click the 'Add Formula' button at top of this worksheet." _
    & " The 'Add Formula' button will add a formula to the 'Disposal Month' in the row you specify." _
    & vbNewLine & vbNewLine & "Alternately if you are editing an existing sample submitted using the form from the home page the formula will already be inserted.", vbCritical

    If Not Intersect(Range("S2:S1048576"), Target) Is Nothing Then MsgBox "'Disposal Quarter' is calculated from the 'Date of Physical Disposal' using a formula." _
    & vbNewLine & vbNewLine & "If you are manually adding a sample to a new blank row please click the 'Add Formula' button at top of this worksheet." _
    & " The 'Add Formula' button will add a formula to the 'Disposal Quarter' in the row you specify." _
    & vbNewLine & vbNewLine & "Alternately if you are editing an existing sample submitted using the form from the home page the formula will already be inserted.", vbCritical
    
    If Not Intersect(Range("T2:T1048576"), Target) Is Nothing Then MsgBox "'Disposal Year' is calculated from the 'Date of Physical Disposal' using a formula." _
    & vbNewLine & vbNewLine & "If you are manually adding a sample to a new blank row please click the 'Add Formula' button at top of this worksheet." _
    & " The 'Add Formula' button will add a formula to the 'Disposal Year' in the row you specify." _
    & vbNewLine & vbNewLine & "Alternately if you are editing an existing sample submitted using the form from the home page the formula will already be inserted.", vbCritical

    If Not Intersect(Range("P2:P1048576"), Target) Is Nothing Then MsgBox "'Date of Physical Disposal' should only be populated with one of the options below:" _
    & vbNewLine & vbNewLine & "1) A date entered in format 'dd/mm/yyyy'." _
    & vbNewLine & "2) Left blank with no text." _
    & vbNewLine & "3) Have the text 'Date TBC' entered.", vbCritical
        
End Sub
Regards
Steve