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

Sub ContinueWhereILeftOff()
    Static iStep as Integer
    Dim iLastStep as Integet

    iLastStep = 9   'or whatever

    If iStep = 0 Then
        iStep = 1
    Endif

    Select Case iStep
        Case 1
            Call StepOneMacro
        Case 2
            Call StepTwoMacro
        ..... Etc.
    End Select
    
    iStep = iStep + 1
    If iStep > iLastStep Then
        iStep = 1
    End If
End Sub

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:

Function CreateButton() As Object
    'Creates a Form Button in Selection Cell(s)
    '   Assigns a universal OnAction
    
    Dim dL As Double _
        , dT As Double _
        , dW As Double _
        , dH As Double
    Dim sh As Worksheet
    
    With Selection
        dL = .Left
        dT = .Top
        dW = .Width
        dH = .Height
        Set sh = .Worksheet
        
        On Error Resume Next
        Set CreateButton = sh.Buttons.Add(dL, dT, dW, dH)
        If Not CreatButton is Nothing Then
            With CreateButton
                .Placement = xlMoveAndSize
                .OnAction = "ButtonClicked"
            End With
        End If
    End With
End Function

Sub ButtonClicked()
    Dim s As String _
            , bnButton As Button _
            , sCaption As String _
            , sName As String

    'Determine what button was clicked
    On Error Resume Next
    s = Application.Caller
    If s <> "" Then
        Set bnButton = ActiveSheet.Buttons(s)
    End If
    On Error GoTo 0

    If Not bnButton Is Nothing Then
        'Get Name and Caption
        With bnButton
            sCaption = .Caption
            sName = .Name
        End With

        'Examples of Use
        '   I suggest using sName, so users can change caption
        '   without any effect on macro
        Select Case sCaption
            Case "New"
                'Call NewData
            Case "Edit"
                'Call EditData
        End Select

        'I suggest renaming buttons also
        '    so you can easily tell what it's supposed to do
        Select Case sName
            Case "Button 1"
                MsgBox "You Clicked 'Button 1'"
            Case "Button 2"
                MsgBox "You Clicked 'Button 2'"
            Case "bnAdd"
                MsgBox "You Clicked 'Add'"
        End Select
    End If
End Sub 

\1