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.