I have a VBA program which performs a sequence of tasks and takes many 10s of minutes to run. I'd like to keep the user apprised of the program's progress.

Each task has VBA code running the Excel Solver. The Solver is using the progress bar.

My idea is to use a version of MsgBox that allows a VBA task to keep running and displays the name of the task. When the task is complete, VBA code closes that MsgBox without user intervention or the use of timers. VBA code then opens a new MsgBox with the name of the next task, etc.

I found a URL that states that the following will allow "macro" code to keep running. If that were so, I would expect to see "3" in a MsgBox when the following code is executed. All I see is a "1". I have to press 'enter' to close "1" and see "2", etc.

Sub msgboxes()

    MsgBox "1", vbApplicationModal
    
    MsgBox "2", vbApplicationModal
    
    MsgBox "3", vbApplicationModal

End Sub

Perhaps there is a better choice than MsgBox to accomplish this.

I have a VBA program which performs a sequence of tasks and takes many 10s of minutes to run. I'd like to keep the user apprised of the program's progress.

Each task has VBA code running the Excel Solver. The Solver is using the progress bar.

My idea is to use a version of MsgBox that allows a VBA task to keep running and displays the name of the task. When the task is complete, VBA code closes that MsgBox without user intervention or the use of timers. VBA code then opens a new MsgBox with the name of the next task, etc.

I found a URL that states that the following will allow "macro" code to keep running. If that were so, I would expect to see "3" in a MsgBox when the following code is executed. All I see is a "1". I have to press 'enter' to close "1" and see "2", etc.

Sub msgboxes()

    MsgBox "1", vbApplicationModal
    
    MsgBox "2", vbApplicationModal
    
    MsgBox "3", vbApplicationModal

End Sub

Perhaps there is a better choice than MsgBox to accomplish this.

• vba
• msgbox

• 2 You would have to create a custom form that looks like a MsgBox instead. Code will not keep running while MsgBox is displayed. It's not possible. – braX Commented Jan 28 at 21:24
• 1 Alternatively, you can use the application's status bar to display progress. That's a much better solution for what you need. stackoverflow/questions/5181164/… – braX Commented Jan 28 at 21:27
• Each task has vba running the excel solver. The solver is already using the progress bar. – Christopher Paul Commented Jan 28 at 22:04
• 1 Excel-VBA-ProgressBar is easy to use. This is the related Code Review question – Cristian Buse Commented Jan 30 at 9:11

2 Answers 2

Here's a very basic example:

Add a userform to your project with a label lblMsg and name the form frmProgress. Add this to the userform's code module:

Public Sub DoMessage(msg As String)
    Me.lblMsg.Caption = msg
    Me.Repaint
End Sub

Example usage (in a regular code module):

Sub tester()
    Dim frm As frmProgress
    
    Set frm = New frmProgress
    frm.Show False             'Non-modal form display allows 
                               '   your code to continue
    frm.DoMessage "Running step 1"

    'set off your first process here

    frm.DoMessage "Running step 2"

    'set off your second process here

    'etc etc

    Unload frm      'unload when done...
End Sub

I followed vba code that made a change in an excel worksheet that I wanted displayed with:

Application.ScreenUpdating = True
Application.Wait Now + TimeValue("0:00:01") ' wait 1 second
Application.ScreenUpdating = False

If the vba code starts from a routine executed from a button on a worksheet different from the one I wanted displayed, I got to the worksheet ws to be displayed with

Dim sh1 As Worksheet: Set sh1 = Sheets("ws") 

sh1.Visible = True
sh1.Select