deactivate Excel VBA userform

Question

I am having some macro in Excel vba and in that I am performing some functions on the excel sheets which takes around 30 seconds to complete. So I want to show a user form with a progress bar during that span of time.

I tried using userform.show in very start of the function and userform.hide at the end but I found that No action can be performed in background.

So just want to know if there is any turn around to let the processing be done in the background while the form is being displayed.

Many thanks :)

Private Sub CommandButton1_Click()
    '--------------Initialize the global variables----------------

   UserForm1.Show



    nameOfSheet2 = "Resource Level view"
    nameOfSheet3 = "Billable Hours"
    nameOfSheet4 = "Utilization"
    '-------------------------------------------------------------
    Dim lastRow, projectTime, nonProjectTime, leaveAndOther
    Dim loopCounter, resourceCounter
    lastRow = 0
    projectTime = 0
    nonProjectTime = 0
    leaveAndOther = 0
    resourceCounter = 2
    Set workbook1 = Workbooks.Open(File1.Value)
    Sheet3Creation
    Sheet2Creation
    Sheet4Creation
    UserForm1.Hide

End Sub

can try userform.show Modeless? What do you mean you cant run anything in the background? Meaning you cant interact with the sheet? — bonCodigo, Jan 17 '13 at 10:03
yes actually once the userform is visible I can't activate the sheets and can't work on that — codeomnitrix, Jan 17 '13 at 10:08
hey great that worked but i can't see the contents of the user form now — codeomnitrix, Jan 17 '13 at 10:11
Are you using the `Do Events` to update the form? :) Can you show us your code please? — bonCodigo, Jan 17 '13 at 10:19
Hi boncodigo i have added the macro code could you please check it now — codeomnitrix, Jan 17 '13 at 10:23
Sure, but looking at your code the Form doesn't seem to work like a progress bar ;) Do you want me to add a label and re-arrange the code for you? All your variables are in default data type, can define what they are really? `integers longs`? Are you doing anything on `Form_Activate event`? Because you said you can't see teh contents of ther user form.. — bonCodigo, Jan 17 '13 at 10:25

score 6 · Accepted Answer · edited May 23 '17 at 11:45

The usage of Progress Bar is to show the progress of currently running code. And I wouldn't know if anyone want to do anything with the sheet while the code is running...

Anyway if you want to interact with the sheet while Form is displaying you may try to add the following code:

 UserForm.Show vbvModeless

And to update a Modeless form you must add DoEvents within your subroutine.

When you want to close the form at the end, do this:

 UserForm.Unload

Here is what I would do:

Click a button to run your macro

Private Sub Button1_Click()
   Call userform.show vbMmodeless
End Sub

Private Sub UserForm_activate()
    Call Main '-- your macro name
End Sub 

Sub Main()
'-- your code
DoEvents '-- to update the form *** important

useroform.Unload
End Sub

After OP showed his code:

Why do we need a progress bar?

When macros take a long time to run, people get nervous. Did it crash? How much longer will it take? Do I have time to run to the bathroom? Relax...

In your case I do not really see that you are using any sort of heaving codes running at the background. So adding a progress bar could make your code slow as to update it, you may be calling an extra loop... check this reference article if you really want to have the progress bar :),
You can also use Application.StatusBar to display a message.
The other is to use Timer or a littel bit more technical way would be to wrap system timer ticks and refresh/update form accordingly. In VBA Excel we don't get that lucky as for C# or VB..
VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds.
How do I show a running clock in Excel?
How do you test running time of VBA code?

OK great, thanks boncodigo, I would write the code for updating the the process bar. Thanks a lot for your help. — codeomnitrix, Jan 17 '13 at 11:06
@codeomnitrix glad it could give you some insights :) I just updated with some relevant info if you are interested do take a look. — bonCodigo, Jan 17 '13 at 14:19

deactivate Excel VBA userform

1 Answers1

Why do we need a progress bar?

Linked