msgbox as a progress indicator...

[Guest]

In other posts I have seen that the status bar has been suggested as the
prefered way to show macro progress...

The VBA that I am running swiches between several workbooks and with Screen
updating turned off, the WB that is open when the macro starts... is not the
one that has focus(in the background) where the status bar would display (if
it even would show changes with screen updating off).

what would be a good way to have the current section being executed always
show?

I'd like to have a msgbox popup for this, but I dont want the "OK" button to
sit there a wait for a press. Is there some way to define a seperate box or
form that can display the status message?

Will I have to toggle screen updating on to show it, then toggle it back off?
and, I assume that I would have to swap the active workbook.worksheet to
make sure it has the focus.

suggestions?

 

[Bob Phillips]

Why not use the standard userform progress indicator, such as at
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

The statusbar is in the application window. It will be visible and active
even with screenupdating turned off.

To demonstrate, open 5 or six workbooks and run this macro:

Option Explicit
Sub ShowStatus()
Dim bk As Workbook
Dim i As Long
Dim r As Long

Application.ScreenUpdating = False
For i = 1 To 10000000
If i Mod 1000000 = 0 Then
r = Int(Rnd * Workbooks.Count + 1)
If Workbooks(r).Windows(1).Visible Then _
Workbooks(r).Activate
Application.StatusBar = ActiveWorkbook.Name & _
" " & i
DoEvents
End If
Next
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub