Run a macro the first time a sheet is opened, but not after.

tsweeney

New Member
Joined
Aug 28, 2008
Messages
39
All,

I have one more quick question.

One of our sheets has a Worksheet_Activate event. I.E. You click on the tab for the sheet "Monthly Summary" and it runs a macro with a msgbox that asks whether you want to update the information that the sheet relies on. This macro completes by returning to the first page of the sheet, and not to the one that has the event

Then, when you go to check that sheet, it brings up the box again. Is there a way to make it so once it is run, you can go to the sheet without triggering the Worksheet_Activate?

Code:
Private Sub Worksheet_Activate()
ISOK = MsgBox("OK TO UPDATE 'CONTENTS OF ACCESS' WORKSHEET", vbYesNo)
If ISOK <> 6 Then Exit Sub
Call TransferTableFromAccess
End Sub

I think it can be worked around by just having the sheet default return to the "Monthly Summary" sheet after doing so, but I'd really like a more elegant way to do this. Because I can easily see a scenario where someone switch between sheets multiple times and it would be really annoying to have the msgbox pop up every time.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,915
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
One way would be to use an out of the way cell on that sheet to hold a count of how many times the sheet has been activated since the workbook was last opened. Then in the activate event code include a line that adds 1 to the count and causes an immediate exit from the sub if that count is greater than 1. In a workbook before close event or a workbook open event have that counter reset to 0.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
Not like you have it there no. Perhaps run the sub off a button on the sheet? Or run it off the workbook open event?
 

tsweeney

New Member
Joined
Aug 28, 2008
Messages
39
Thanks Joe, I'll try that. Yeah, my sheet is being put through the wringer and inspection by the resident Excel/Database/etc. guru here to become part of his conglomerate program and that workbook activate was something he was trying. I'll let you know what the results were.

And Steve, that was pretty much what I thought. I'll probably end up doing a button on that sheet eventually. I had it as a Workbook open event and it started to get too slow for the purposes of the users who input the data. They couldn't care any less about the back end details, so we were trying to find a way that the data could still be updated for analysis purposes but not at the expense of a greater loading time for when you're just entering data.
 

tsweeney

New Member
Joined
Aug 28, 2008
Messages
39
Setting up the counter was simple enough and does exactly what I wanted. Thank you to both of you!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,445
Members
430,549
Latest member
jayjay2022

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