Lock in VBA

[Guest]

Hello,

Is it possible to include in a VBA macro a command to would block an user to
interacte with the Excel window, while a VBA macro is running ?
Jeff

 

[Harald Staff]

Hi Jeff

You might show a (modal) userform while the thing runs. Preferrably one that
shows "working" or the progress of your code.

HTH. Best wishes Harald

 

[Guest]

Hello,

I already have an userform. Can I write something in my macro ?
Thanks,

 

[Harald Staff]

Hi again

Well, you cant lock everything down. But see if this does it.
In the Userform1 module (change all over to fit your userform name):

Private Sub UserForm_Activate()
Me.Caption = "Working..."
Me.Repaint
DoEvents
Call Macro1(True)
End Sub

And in a standard module:

Sub Runit()
UserForm1.Show
End Sub

Sub Macro1(X As Boolean)
Dim L As Long
Application.Visible = False
DoEvents
'sample time consuming loop:
'replace with real action:
For L = 1 To 10000
ActiveSheet.Cells(1, 1).Value = L
Next
'done, keep this:
Application.Visible = True
Unload UserForm1
End Sub

HTH. Best wishes Harald