VBA. Create a msgbox so that it only appears once when the generated cell changes
I am new to VBA and I am trying to create a msgbox that will react if a change is made to a column. This column presents the results obtained by the formulas and the results are presented in the text. For example, a formula consists of checking two balances. If they match, the worded cell will show "Co-local", if they don't, it will show "Not matched" instead. When it is changed from "Co-local" to "Not mapped", I would like to show msgbox to remind who made the wrong balance change. However, here's the problem, when I run my code below, the msgbox loops and displays multiple times in the result for every change made. How can I display it only once for every change made from "Co-local" to "Not agreed. "Here is my code:
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Set rr = Range ("L6:L57").Cells.SpecialCells(xlCellTypeFormulas)
For Each r in rr
If r.Value = "Not Matched" Then
MsgBox "Please Checked Date Entered", vbExclamation
End If
Next r
End Sub
Please, help!
Significant score.
source to share
Try this code
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Dim bNotMatched as Boolean
Set rr = Range ("L6:L57").Cells.SpecialCells(xlCellTypeFormulas)
bNotMatched = false
For Each r in rr
If r.Value = "Not Matched" Then
bNotMatched = true
End If
Next r
If bNotMatched = true then
MsgBox "Please Checked Date Entered", vbExclamation
End if
End Sub
source to share
My idea is to have a copy of the Consistent / Not Mapped column with static values, and every time you check the Not Mapped value in rr
you can compare that value with the copy. If both "Not the same", go to the next one r
. If not, then change "Co-local" to "Not Mapped" in the copied column and display msgbox.
source to share