Make Message Box Only Appear Once

[Cgar626]

Hello,

I was able to program a message box to appear based on a particular cell value, but that same message box appears every time I click on a cell. It will continuously appear until the initial cell conditions are no longer met. How do I make it so that the message only appears once?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B4") = "This household will qualify for the program even if their income is counted." Then

MsgBox "Please direct them to come to the office.", vbOKOnly

End If

End Sub

 

[Joe4]

Welcome to the Board!

You are using a "Worksheet_SelectionChange" event procedure, which is VBA code that by definition, runs whenever a cell is selected on your sheet.
You probably need to use a different cell procedure. But before we can determine which one and how it should be be written, we need to understand how the data is being updated.
How exactly is cell B4 being updated? Is it a formula?
If it is, what is the exact formula?

 

[Cgar626]

Yes, it is a formula.

=IF(D87="Certification periods cannot exceed 36 months.","",IF(AND(Nonrecurring_Medical>0, C87=""),"Complete the table at the bottom of this spreadsheet to determine the optimal proration of non-recurring medical expenses.",'Optimal Calculation Table'!A30))

But unfortunately, it's a formula that calls on a bunch of other cells with formulas. Because I'm so new to this, I ended up complicating it more than I should have. I don't want you to have to rack your brain with all these different formulas, so perhaps you could give me a general example of what to do?

 

[Cgar626]

I just changed the procedure to "Change" and it seems like it solved the problem. But if you have more information to offer me, please do!

 

[Joe4]

I just changed the procedure to "Change" and it seems like it solved the problem. But if you have more information to offer me, please do!

If you changed nothing else, that will only run when a cell is manually changed. However, if you manually change multiple cells, it could run multiple times.

Here is the key. The "Worksheet_Change" event procedure runs whenever any cell is MANUALLY updated (so formula updates will not trigger it).
So, what we need to do it determine when you actually want this code to run - what is the action that should trigger the code to run.
It may be the manual update of another cell that is used by your formula in cell B4.
Also, both the "Worksheet_Change" and "Worksheet_SelectionChange" procedure have a range parameter named "Target". Basically, this is the range that triggers the code to run.
For "Worksheet_Change", it is the range that is manually updated.
For "Worksheet_SelectionChange", it is the range that is selected.

Let me give you a simple example of we can set up something to do something like what you want. Let's say that cell B4 has the following formula:

=B2+B3

And you want the code to run once if the value in B4 is greater than 10. So we can watch cells B2 and B3 for manual updates (since they are the cells used in the formula in B4).

So we could have VBA code like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   See if an update is made to cell B2 or B3
    Set rng = Intersect(Target, Range("B2:B3"))

'   If not, exit sub
    If rng Is Nothing Then Exit Sub
    
'   Check to see if value in B4 is greater than 10
    If Range("B4").Value > 10 Then
        MsgBox "VBA code fired!"
    End If

End Sub

Does that help/make sense?

 

[Cgar626]

If you changed nothing else, that will only run when a cell is manually changed. However, if you manually change multiple cells, it could run multiple times.

Here is the key. The "Worksheet_Change" event procedure runs whenever any cell is MANUALLY updated (so formula updates will not trigger it).
So, what we need to do it determine when you actually want this code to run - what is the action that should trigger the code to run.
It may be the manual update of another cell that is used by your formula in cell B4.
Also, both the "Worksheet_Change" and "Worksheet_SelectionChange" procedure have a range parameter named "Target". Basically, this is the range that triggers the code to run.
For "Worksheet_Change", it is the range that is manually updated.
For "Worksheet_SelectionChange", it is the range that is selected.

Let me give you a simple example of we can set up something to do something like what you want. Let's say that cell B4 has the following formula:

=B2+B3

And you want the code to run once if the value in B4 is greater than 10. So we can watch cells B2 and B3 for manual updates (since they are the cells used in the formula in B4).

So we could have VBA code like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   See if an update is made to cell B2 or B3
    Set rng = Intersect(Target, Range("B2:B3"))

'   If not, exit sub
    If rng Is Nothing Then Exit Sub
   
'   Check to see if value in B4 is greater than 10
    If Range("B4").Value > 10 Then
        MsgBox "VBA code fired!"
    End If

End Sub

Does that help/make sense?

Yes that’s helpful. Thank you so much

 

[Joe4]

You are welcome.
Here is a good write-up on Events: Events In Excel VBA