How to show a wait screen in VBA Excel 2010

Question

I have macros that are running on some workbooks and a status screen that shows their progress so the user does not have to stare at a blank screen or the loading screen of Excel. Or so I though. The status page works, it will update as the macros run, but excel will not show any of it until after the macros finish running. How do I show my status screen?

status = "SheetName"

Private Sub Workbook_Open()
    'Make sure that the screen is active
    Sheets(status).Activate
    Sheets(status).Select
End Sub

Answer 1

If the purpose of your status screen is simply to give some feedback while your macros are running, a quick and easy alternative is to use the Status Bar. Here's a sample:

Sub YourMacro()
    Dim StatusOld As Boolean, CalcOld As XlCalculation

    ' Capture Initial Settings
    StatusOld = Application.DisplayStatusBar

    '      Doing these will speed up your code
    CalcOld = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo EH

    ' Your code...

    ' Every so often while your code is running
    Application.StatusBar = "Something Useful..." 

    ' After all your code is done
CleanUp:
    ' Put things back like they were
    Application.StatusBar = False
    Application.Calculation = CalcOld
    Application.DisplayStatusBar = StatusOld
    Application.ScreenUpdating = True
    Application.EnableEvents = True
Exit Sub
EH:

    ' Your error handler...

    GoTo CleanUp
End Sub

Answer 2

You have to call DoEvents after each update (VBA instruction). That will give processor time back to Excel for an instant, and it'll use it to redraw the screen. You do have to be a little careful with this as it'll also allow your user to click in the spreadsheet and mess things up.