Pause and resume between series of macros

I'm looking for the best way to allow a user who doesn't know VBA to start up different series of macros.

The big picture is a group of users have to create their own budget forecast. There are multiple phases involved, and multiple steps in each phase. Phase 1 is creating a Staffing forecast, and all the steps to creating it I can do in a series of macros, initiated by the user selecting options from a drop down box. Once it's created, that forecast will be used to drive the creation of other forecasts for travel, supplies, etc.

The problem is this; once the Staffing forecast is created the user will need to eyeball it and make adjustments for things that can't be automated (ie his boss told him this morning that 20 unfilled Widget Maker positions aren't going to be filled, ever). I want the user to be able to make his changes to the staffing forecast and then start the next series of macros (for instance, to create his travel forecast). How do I put a "Go" button of some kind on his sheet to allow him to simply start the next series of macros? And there will be other instances where he'll need to stop at some point in the forecast creation to tweak a particular forecast, so I'd need to again be able to resume the macros after a pause for tweaking.

I'm open to any ideas, and appreciate all the help I've gotten from this group in the past. I'm hoping for a miracle here!

Hi jomili;

Here's 1 way to accomplish what you want.

Put a static variable in 1 macro and let them call only that macro

Please Login or Register  to view this content.

If you want to create buttons on the fly, let me know. I'll give you my macro that builds buttons and assigns a macros to the buttons, etc.

Why don't you just use a menu system with a button for each of the phases?

I mean menus - as in those things sitting at the top of the screen in Excel.
(File, Edit, Insert etc)

Have a look at J-Walk's tip here. Should give you everything you need.

Yep - that's why he is (until the end of this month anyway) an Excel MVP!

Yep - see here. Damn shame, IMO as he's one of the real MVPs to my mind.

Hi jomili;

I agree that a menu system is a better choice than a Static variable. Menus will let the user close a file and when they open the file they can start back where they left off. A static variable would be reset to 0 when they closed the file, so they would have to restart at step 1 when they reopened the file.

If you still want to see my macros for building buttons, I'll post them when I return in a few hours.

John asked that I post my button macros, so here they are:

Please Login or Register  to view this content.

\1