ExcelBanter - Message Box Coding question

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Message Box Coding question (https://www.excelbanter.com/excel-programming/372301-message-box-coding-question.html)

Message Box Coding question

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

Message Box Coding question

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

"Michael" wrote in message
oups.com...
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

Message Box Coding question

can also make it a procedure static variable.

--
HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
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

"Michael" wrote in message
oups.com...
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

Message Box Coding question

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

RBS

"Bob Phillips" wrote in message
...
can also make it a procedure static variable.

--
HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
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

"Michael" wrote in message
oups.com...
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

Message Box Coding question

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" wrote in message
...
Yes, not sure why now, but I somehow prefer the Private one.

RBS

"Bob Phillips" wrote in message
...
can also make it a procedure static variable.

--
HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
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

"Michael" wrote in message
oups.com...
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

Message Box Coding question

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
Yes, not sure why now, but I somehow prefer the Private one.

RBS

"Bob Phillips" wrote in message
...
can also make it a procedure static variable.

--
HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
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

"Michael" wrote in message
oups.com...
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