Excel - Modal forms

[Lee_S]

Hi,

Is it possible to load a form in excel as Modal, but have the assigned macro continue to run in the background?

I have a command button on a worksheet that starts a macro and opens a userform. I want the userform to show the progress of the macro in a textbox, and when done enable a button on the form. But i dont want the user to be able to move around the workbook whilst its running.

cheers

[Ecniv]

Should be possible... in theory at least.

Userform.show vbmodal

I think. Try that and see whether the code stops until the form is closed.

Alternatively there are two properties of excel, um updatewindow and supresserrors (or view errors or something) which stop the reporting and updating (scrolling in the background...)

If you disable the update, but force a refresh on the form it might update...

[DKenny]

Have you considered using the StatusBar to show the progress and only show the form when the macro has completed?

Sub SampleStatusUpdate()
Dim MyStep As Integer
Dim AllSteps As Integer
Dim OldStatusBar As Boolean
 
OldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
 
AllSteps = 10
For MyStep = 1 To AllSteps
    Application.StatusBar = "On step " & CStr(MyStep) & " of " & CStr(AllSteps) & "..."
    MsgBox "Hi"
Next MyStep
 
Application.StatusBar = False
Application.DisplayStatusBar = OldStatusBar
 
End Sub

[RobDog888]

Or you can look at it from the other end. Have the button show the form modal but inside the form have the macro code. This will keep the user from doing anything else and yet let the macro code run in the context of the form.