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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Setting up the counter was simple enough and does exactly what I wanted. Thank you to both of you!
 
Upvote 0

Forum statistics

Threads
1,217,452
Messages
6,136,724
Members
450,025
Latest member
Beginner52

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
Back
Top