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

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
Try

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,485
Office Version
  1. 2019
Platform
  1. Windows
Hi Try

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
Dave, thanks for pointing out the .ID property of the Range. Using it like a .Tag is clever.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,485
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,485
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,759
Messages
5,626,695
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top