Pause and resume between series of macros

**jomili** · 09-10-2010, 01:13 PM

No responses at all? I've thought of, at the end of first process putting up a MsgBox that says something like [CODE]"Staffing forecast done; make adjustments, then do something to start next phase"]/CODE]"

I've thought of maybe having a series of shortcut codes appear on the message box, or maybe at the bottom of the sheet having the focus. Or, having buttons appear (but then I'd have to do a macro to create them, and don't know how to do that), or have a UserForm allowing them to select the next phase. But for that I'd have to find a way to evoke the UserForm that they could do simply.

I'm wide open to ideas, and I know there's folks much smarter than me out there. Help me, please!

**foxguy** · 09-12-2010, 05:09 PM

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.

**jomili** · 09-13-2010, 08:58 AM

hey Foxguy,

Thanks for responding. I'm not sure I understand how your "ContinueWhereILeftOff" macro works, but if it works the way I think it would I'd have to have a button or shortcut for them to call that, but that would be the only button or shortcut they'd need, correct? I'd appreciate it if you could explain that one to me.

I'd love to see your macro that builds buttons and assigns macros to the buttons. Would you mind providing it?

I'm sure between that on your "Continue..." macro I can get it done. Thanks so much for your help!

John