| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO PROMPT MSGBOX JUST ONCE
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO PROMPT MSGBOX JUST ONCE
On Jun 14, 11:14 am, K wrote:
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. 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO PROMPT MSGBOX JUST ONCE
On 14 Jun, 19:20, Leith Ross wrote:
On Jun 14, 11:14 am, K wrote: 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. 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 - 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO PROMPT MSGBOX JUST ONCE
On Jun 14, 1:44 pm, "Don Guillett" wrote:
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "K" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO PROMPT MSGBOX JUST ONCE
On Jun 14, 1:44 pm, "Don Guillett" wrote:
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "K" wrote in message ... 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. 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO PROMPT MSGBOX JUST ONCE
On 15 Jun, 02:12, Leith Ross wrote:
On Jun 14, 1:44 pm, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "K" wrote in message ... 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. 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 - Thanks guyes. it worked |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB - ignore a msgbox prompt | Excel Discussion (Misc queries) | |||
| Entering variables on Msgbox prompt | Excel Programming | |||
| Msgbox prompt and tabbing | Excel Programming | |||
| MsgBox Prompt w/ No "X" to close | Excel Programming | |||
| msgbox prompt when user selects data from combo box | Excel Programming | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
