Only display MsgBox once, not every time a sheet is opened

[gtd526]

I have a macro in 'ThisWorkbook' that will activate a MsgBox each time a worksheet is selected.
How can I have this MsgBox activate only once, not every time the sheet is selected?

Code Used:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'when a worksheet is opened
'shortcuts' worksheet in 'formulas' workbook

If Sh.Name = "Shortcuts" Then
MsgBox "My macro works when you open a worksheet not a workbook "
Else
End If

End Sub

 

[jasonb75]

Try

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Static RunOnce As Boolean
If RunOnce = True Then Exit Sub
RunOnce = True
'when a worksheet is opened
'shortcuts' worksheet in 'formulas' workbook

If Sh.Name = "Shortcuts" Then
    MsgBox "My macro works when you open a worksheet not a workbook :)"
Else

End If

End Sub

 

[dmt32]

Hi Try

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'when a worksheet is opened
'shortcuts' worksheet in 'formulas' workbook

    If Sh.Name = "Shortcuts" Then
        With Sh.Range("A1")
            If Val(.ID) = 0 Then MsgBox "My macro works when you open a worksheet not a workbook :)": .ID = xlOff
        End With
    End If
End Sub

Solution re-sets itself when workbook is closed

Dave

 

[mikerickson]

Dave, thanks for pointing out the .ID property of the Range. Using it like a .Tag is clever.

 

[gtd526]

ADVERTISEMENT

Thank you both for your reply.
Both macros work.
Now I have to decide which one to use

 

[dmt32]

Dave, thanks for pointing out the .ID property of the Range. Using it like a .Tag is clever.

That’s very kind of you but I don’t think I can claim it as an original idea – I came across Range.ID property sometime ago whilst browsing the VBA helpfile & have used it as a placeholder (like Tag) for number of solutions - certainly can be useful.

Dave

 

[dmt32]

Thank you both for your reply.
Both macros work.
Now I have to decide which one to use

Glad we were able to help - which solution you use is for you to decide.
Thanks for your feedback it is always appreciated.

Dave