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

[tsweeney]

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?

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.

 

[JoeMo]

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]

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]

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]

Setting up the counter was simple enough and does exactly what I wanted. Thank you to both of you!