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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
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,112
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,311
Members
409,862
Latest member
lbisacca
Top