VBA Message Box - restrict to one occurrence

[mizogy]

Hi All

I have a simple msgbox macro which is called upon each time a worksheet is activated. Users could select any worksheet in any particular order at any given time. Currently, the message box appears several times over should the user interchange between different sheets in the workbook. What I would like to be able to do, is restrict the macro containing the msgbox appearing from appearing again after one occurrence in the entire workbook. Help would be appreciated. Am I able to define the macro itself as variable so it only loops once?

Private Sub Worksheet_Activate()

Call MsgBox_Governance

End Sub


Sub MsgBox_Governance()

MsgBox "Governance:" & vbCrLf & vbCrLf & _

"Please ensure sign-off is obtained before proceeding", vbInformation, "User Information"

End Sub

Best, Mizogy

 

[dmt32]

Hi,
see if this update to your code will do what you want

Sub MsgBox_Governance()
    Static showonce As Long
    
    If showonce <> xlOff Then
        MsgBox "Governance:" & vbCrLf & vbCrLf & _
               "Please ensure sign-off Is obtained before proceeding", vbInformation, "User Information"
        showonce = xlOff
    End If
    
End Sub

Dave

 

[DanteAmor]

Hi @mizogy

In your sheet module, change your code to the following code:

Public isactivated As Boolean

Private Sub Worksheet_Activate()
  If isactivated = True Then Exit Sub
  isactivated = True
    
  Call MsgBox_Governance
End Sub

Sub MsgBox_Governance()
  MsgBox "Governance:" & vbCrLf & vbCrLf & _
    "Please ensure sign-off is obtained before proceeding", vbInformation, "User Information"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[mizogy]

Hi,
see if this update to your code will do what you want

Sub MsgBox_Governance()
    Static showonce As Long
   
    If showonce <> xlOff Then
        MsgBox "Governance:" & vbCrLf & vbCrLf & _
               "Please ensure sign-off Is obtained before proceeding", vbInformation, "User Information"
        showonce = xlOff
    End If
   
End Sub

Dave

Perfect - that worked. Thank you, appreciated.

 

[mizogy]

Hi @mizogy

In your sheet module, change your code to the following code:

Public isactivated As Boolean

Private Sub Worksheet_Activate()
  If isactivated = True Then Exit Sub
  isactivated = True
   
  Call MsgBox_Governance
End Sub

Sub MsgBox_Governance()
  MsgBox "Governance:" & vbCrLf & vbCrLf & _
    "Please ensure sign-off is obtained before proceeding", vbInformation, "User Information"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Thank you, appreciated.

 

[dmt32]

Perfect - that worked. Thank you, appreciated.

most welcome glad we were able to help & appreciate your feedback

Dave