Hi,
This is my first experience with VBA macros so bear with me if my jargon isn't right. I've got a problem with nesting macros involving an external data connection (see attached screencap and code).
I have a macro to update the data connection to a website and change cell A1 to today's date in static text (Update_website). I have other macros to turn on and off sheet protection on multiple sheets (Sheets_lock and Sheets_unlock) and to perform some tools from the Data tab (Static_month and Static_day). All of these work.
The problem is my nested macro (Do_everything) that uses all the other ones in unlock-edit-relock sequence. I get the error message telling me that I'm trying to edit a locked sheet. I thought perhaps it was trying to run the update before it finished unlocking the sheets so I experimented with putting in a Wait delay, but that didn't help. After further experimentation with delays in various places, not having it lock at the end, etc I've discovered that no matter what order I code things in the last thing to get done is update the external data. Even in the Update_website macro, the A1 cell gets changed before the website connection is updated even though it's coded to happen afterward.
As my calculations depend on having current data it's crucial for the Update_website to finish before the other macros start. Am I missing something obvious? Thank you.
Bookmarks