CODEWITHSUNDEEP
excelvba
ahorsewith2names
ahorsewith2names

Reputation: 23

MsgBox shows up every time new data is added when I only need it to pop up once

I have a macro that has a MsgBox pop up if a cell value > 15. It works when the cell Q5 I check is >15 but with each additional data entry the MsgBox pops up. Is there a way to have it so the MsgBox only pops up once? Maybe after the value is > 15 in checks again in a time interval?

Code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("Q5").Value > 15 Then
        MsgBox "Please submit write-off form"
    End If

End Sub

MsgBox pops up after every new data entry. I want it so it only pops up once after the value of Q5 > 15 and after additional data is entered.

Upvotes: 1

Views: 50

Answers (2)

ahorsewith2names
ahorsewith2names

Reputation: 23

Option Explicit
    Private Sub Worksheet_Calculate()
    
        If Sheet1.CustomProperties.Count < 1 Then
            Sheet1.CustomProperties.Add "Bool", True
        Else
            If Sheet1.CustomProperties(1).Value = True Then
                If Range("Q5").Value > 14 Then
                    MsgBox "Please submit write-off form"
                    Sheet1.CustomProperties(1).Value = False
                End If
            End If
        End If
        
        If Range("Q5").Value < 15 Then
        Sheet1.CustomProperties(1).Value = True
        End If
    End Sub

Thanks Cameron! I added an additional If to help with the reset.

Upvotes: 1

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

I'm not sure if this is the common usage of Sheet#.CustomProperties , but I've been dying to see if it works well as MISC sheet memory.
Well it seems it does:

Option Explicit
Private Sub Worksheet_Calculate()
    If Sheet1.CustomProperties.Count < 1 Then
        Sheet1.CustomProperties.Add "Bool", True
    Else
        If Sheet1.CustomProperties(1).Value = True Then
            If Range("Q5").Value > 15 Then
                MsgBox "Please submit write-off form"
                Sheet1.CustomProperties(1).Value = False
            End If
        End If
    End If
End Sub

This will ONLY fire once. The first time Q5 > 15.

If you want to reset it so it will fire again, use:
Sheet1.CustomProperties(1).Value = true
You can even just do this in the immediate window.

Upvotes: 1

Related Questions