CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 109

Trigger MsgBox upon first entry of cell, do nothing on subsequent changes or clicks of the cell

Update:

I have removed the original question text as that is resolved, but has lead to a new question.

My code is

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A15")) Is Nothing Then
    MsgBox "something changed"
End If

End Sub

This triggers the MsgBox when I click on the cell for the first time, which is great, but subsequent changes or clicks to that cell also triggers, and I'm trying to make it so it only triggers on the first click/first entry of data, subsequent changes or clicks to the cell should do nothing

Upvotes: 0

Views: 311

Answers (2)

Legxis
Legxis

Reputation: 916

If you want a MsgBox to appear the first time you click on a cell, an easy way to do that would be to check if the cell is empty before calling the MsgBox. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A1000")) Is Nothing _
   And IsEmpty(Target) Then

    MsgBox "something changed"

End If

End Sub

But then what happens if the user clicks on the cell, the MsgBox shows, but the user doesn't enter any value into the cell? Do you want to show the MsgBox again the next time they click on it or not?

If you don't want to show it again, then rather than testing if the cell is empty, I would fill a global range with all the cells that have been clicked so far. Then you would only need to check if the cell is already contained in the range.

Public clickedCells As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' if the selected cell is one of the relevant cells
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then

'   usual procedure if the range of clicked cells is still empty
    If clickedCells Is Nothing Then

        MsgBox "something changed"
        Set clickedCells = Target

    Else

'       if the range of clicked cells is not empty, then check if selected cell is contained in it
        If Intersect(Target, clickedCells) Is Nothing Then

            MsgBox "something changed"

            ' write selected cell into range
            Set clickedCells = Union(clickedCells, Target)

        End If

    End If

End If

End Sub

p.s.: Do you want to show the MsgBox after a value has been typed in a cell for the first time, or at the time of selecting the cell for the first time? Because if it's the former, you should use the 'Change' event instead of 'SelectionChange'.

Private Sub Worksheet_Change(ByVal Target As Range)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You have not indicated if this is the only Event macro you are using or if there are others. If this is the only one, then:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A15")) Is Nothing Then
        MsgBox "something changed"
        Application.EnableEvents = False
    End If
End Sub

EDIT#1:

The code above acts like a mousetrap. Once it snaps, it must be reset manually. So after you have completed edits to the event code, run this manually:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions