Worksheet Open First Time Only

[Analyze This]

Good Afternoon,

I would like to run a routine that renames my sheet names when a certain worksheet is opened / activated. The routine runs just fine, but how can I make it only run once per session? I need to wait until linked data is refreshed before I can rename my sheets, so I cannot add this to a workbook open statement. Any suggestions??

thank you!

 

[Oaktree]

Is there a way to *tell* when you've refreshed your linked data (e.g. the linked data will pull in today's date or something)? If so, you should be able to include a line in your code to check that value and fire the rest of the code in your workbook_open event only if that value meets the desired criterion.

 

[Analyze This]

there would not be anything constant that happens, other than Excel automatically prompting the user to refresh links. I'm not sure how to tie that into the routine - awesome idea though!

 

[Oaktree]

Would changing the setting for updating links (edit --> links --> startup prompt) to "Don't display the alert and update links" circumvent your problem?

 

[Analyze This]

No - I have a routine in workbook.open that must run before the second routine. I don't know how to code for something to run after the prompted link refresh, do you?

 

[Oaktree]

No, but if you want to run something, update links, then run more code, consider using this:

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

in your code.

Try setting the startup prompt to "Don't display the alert and don't update links" and using that code instead of relying on the user's selection.