HOW TO PROMPT MSGBOX JUST ONCE

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hi all, i have macro in Sheet Module (see below)

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select

End If
End Sub

the macro above works fine but problem is that if any user go again
after sheet get Authorised and do some changes in sheet the MsgBox pop
up again. Is it not possible that when some one put some thing in cell
"K24" the MsgBox pop up just once but if some one go againg in the
sheet and do some changing doesnt matter in what cell including "K24"
then MsgBox should not pop up. Please can any one have any idea that
how can i stop MsgBox to appear more that one time. Please note that i
dont want to protect sheet as some time user have to change things
after it get Authorise.
 
Hi all, i have macro in Sheet Module (see below)

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select

End If
End Sub

the macro above works fine but problem is that if any user go again
after sheet get Authorised and do some changes in sheet the MsgBox pop
up again. Is it not possible that when some one put some thing in cell
"K24" the MsgBox pop up just once but if some one go againg in the
sheet and do some changing doesnt matter in what cell including "K24"
then MsgBox should not pop up. Please can any one have any idea that
how can i stop MsgBox to appear more that one time. Please note that i
dont want to protect sheet as some time user have to change things
after it get Authorise.
Click to expand...

Hello K,

Use a Static boolean variable to track if the message has been shown
already.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Static Displayed As Boolean

If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select
Displayed = True
End If

End Sub
 
Hello K,

Use a Static boolean variable to track if the message has been shown
already.
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
  Static Displayed As Boolean

     If Range("K24").Value <> "NOT AUTHORISED" Then
      Range("M4").Value = "AUTHORISED"
      MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
      Range("B27").Select
      Displayed = True
    End If

 End Sub
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------
Sincerely,
Leith Ross- Hide quoted text -

- Show quoted text -
Click to expand...

Hi leith, thanks for replying. i did try the code you send me my
friend but its not working as i am keep getting messages. any
suggestions
 
I don't understand what you are doing and why this way but try this.

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Range("k24")) = "NOT AUTHORISED" Then Exit Sub
Application.EnableEvents = False
If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select
End If
Application.EnableEvents = True
End S
 
I don't understand what you are doing and why this way but try this.

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Range("k24")) = "NOT AUTHORISED" Then Exit Sub
Application.EnableEvents = False
If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select
End If
Application.EnableEvents = True
End S
Click to expand...
 
I don't understand what you are doing and why this way but try this.

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Range("k24")) = "NOT AUTHORISED" Then Exit Sub
Application.EnableEvents = False
If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select
End If
Application.EnableEvents = True
End S
Click to expand...

Hello K,

I forgot to check the status of "Displayed"...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Static Displayed As Boolean

If Displayed = True Then Exit Sub

If Range("K24").Value <> "NOT AUTHORISED" Then
Range("M4").Value = "AUTHORISED"
MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
Range("B27").Select
Displayed = True
End If

End Sub
 
Hello K,

I forgot to check the status of "Displayed"...
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------
Private Sub Worksheet_Change(ByVal Target As Range)

  Static Displayed As Boolean

     If Displayed = True Then Exit Sub

     If Range("K24").Value <> "NOT AUTHORISED" Then
      Range("M4").Value = "AUTHORISED"
      MsgBox "THANKS FOR AUTHORISATION", vbInformation, "AUTHORISED"
      Range("B27").Select
      Displayed = True
    End If

 End Sub
---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------
Sincerely,
Leith Ross- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks guyes. it worked
 
Back
Top