How to create a status dialog in Excel

Question

I created a database report generator in Excel. I am trying to create a dialog box that displays status information as the program starts.

When I create a report, although a dialog box appears, I cannot update / update the information it displays. In most cases, a dialog box appears only partially. I tried using the .repaint method, but still getting the same results. I see only the full dialog after creating the report.

Answer 1

The code below works well when performing actions in Excel (XP or later).

, Excel, , , (, " ", " " )

"frmStatus" , "Label1" .

'ShowModal' = false, .

Sub ShowForm_DoSomething()

    Load frmStatus
    frmStatus.Label1.Caption = "Starting"
    frmStatus.Show
    frmStatus.Repaint
'Load the form and set text

    frmStatus.Label1.Caption = "Doing something"
    frmStatus.Repaint

'code here to perform an action

    frmStatus.Label1.Caption = "Doing something else"
    frmStatus.Repaint

'code here to perform an action

    frmStatus.Label1.Caption = "Finished"
    frmStatus.Repaint
    Application.Wait (Now + TimeValue("0:00:01"))
    frmStatus.Hide
    Unload frmStatus
'hide and unload the form

End Sub

Answer 2

DoEvents . .

Answer 3

Excel ( ), , .

, .

Ok @JonnyGold, , ...

Sub StatusBarExample()
    Application.ScreenUpdating = False 
    ' turns off screen updating
    Application.DisplayStatusBar = True 
    ' makes sure that the statusbar is visible
    Application.StatusBar = "Please wait while performing task 1..."
    ' add some code for task 1 that replaces the next sentence
    Application.Wait Now + TimeValue("00:00:02")
    Application.StatusBar = "Please wait while performing task 2..."
    ' add some code for task 2 that replaces the next sentence
    Application.Wait Now + TimeValue("00:00:02")
    Application.StatusBar = False 
    ' gives control of the statusbar back to the programme
End Sub

, !

Answer 4

, . ""

Sheets("information").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "Updating Records"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")

Sheets("information").Select
Range("C3").Select
Application.ScreenUpdating = True
ActiveCell.FormulaR1C1 = "Preparing Information"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")

Etc - ,

Range("C3").Select
ActiveCell.FormulaR1C1 = "Updating Records"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")

Etc

Answer 5

The dialog box also works in the same user interface thread. Thus, he is too busy to repaint himself. Not sure if VBA has good multithreading capabilities.