Message Box Coding question

[Michael]

Below is the code I use for a Message box when the value of a cell is
greater than a certain number. I only want this Message box to show up
once. I need the code to be modified for that to happen. Will someone
assist me with that?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim rng2 As Range


Set rng = Range("b37") '<<==== CHANGE to suit


On Error Resume Next
Set rng2 = rng.Precedents
If rng2 Is Nothing Then Set rng2 = rng
Set rng2 = Intersect(rng2, Target)
On Error GoTo 0


If Not rng2 Is Nothing Then
If rng.Value > 500 Then
Application.EnableEvents = False
MsgBox "Local Manager Approval Required"
Range("c37").Value = "Local Manager Approved (amount over
$500.00)"
Application.EnableEvents = True
End If
End If
End Sub

 

[RB Smissaert]

At the top of your module put:
Private bDoneMessage as Boolean

Then alter your code like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim rng2 As Range


Set rng = Range("b37") '<<==== CHANGE to suit


On Error Resume Next
Set rng2 = rng.Precedents
If rng2 Is Nothing Then Set rng2 = rng
Set rng2 = Intersect(rng2, Target)
On Error GoTo 0


If Not rng2 Is Nothing Then
If rng.Value > 500 and bDoneMessage = False Then
Application.EnableEvents = False
MsgBox "Local Manager Approval Required"
Range("c37").Value = "Local Manager Approved (amount over
$500.00)"
Application.EnableEvents = True
bDoneMessage = True
End If
End If
End Sub


RBS

 

[Bob Phillips]

can also make it a procedure static variable.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RB Smissaert]

Yes, not sure why now, but I somehow prefer the Private one.

RBS

 

[Bob Phillips]

I think I do, but some people like to restrict module and global variables.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RB Smissaert]

With me it's probably just laziness as the concept of Static is a bit more
complex than Private.
The other thing is that it is easier to see all your persisting variables in
one place.

RBS