Making macro's run while userform is open and active.

L

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
OK, I have a sheet with some pretty time consuming macro's which run to configure the layout, details and maths on many sheets depending on user input. I know that my code is probably the cause of most of the time taken to run these macro's, but I am new to using macro's and VB in Excel and still learning good code structure etc. So anyway, the upshot is that i have pieced ogether a progress bar from other buts of code i found on the net. The diference is my progress bar does not try to time itself to the macro that is running but rather just goes to 100% then returns to zero and goes up again. In the code for the progress bar, i use two loops, one which determins the speed the bar rises at, and the second determins how many cycles the bar should run for. My aim was to use the bar for all my macro's so it appeared whenever a macro was run and then disappear again when the macro was complete. I have now learned that when I trigger the progress bar (its done using a userform) the macro that triggers it stops and waits for the progress bar to finish running before continuing. This obviously defeats the object of the exercise and of course makes running the macro's even longer than normal.

So my question is: Is there a way to let my original macro continue to run while the progress bar is displayed?

Sounds easy, Or maybe I'm more dumbererer than i give myself credit for.

Any help much appreciated.

Lee.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
This is how a progress bar can be implemented:
- design the progress bar userform (mine includes a progressbar control and a label, together inside a frame, the progressbar shows the progress, the label shows different messages, the frame caption shows percentage complete, the formtitle shows the processname).
- add a module for all progress bar funtionality, with the following code:

Code: 
Public Sub ActivateProgressBar(ProgressCaption As String)
    FrmProgressBar.Show
    FrmProgressBar.Caption = ProgressCaption
    FrmProgressBar.Repaint
End Sub
 
Public Sub SetProgress(PercentageCompleted As Long, ProgressMessage As String)
    If PercentageCompleted > 100 Then PercentageCompleted = 100
    With FrmProgressBar
        .PgbMainProgress.Value = PercentageCompleted
        .FraMainProgress.Caption = Format(PercentageCompleted / 100, "0%") & " Completed"
        .LblMainProgressText.Caption = ProgressMessage
        .Repaint
    End With
End Sub
 
Public Sub ResetProgress()
    With FrmProgressBar
        .PgbMainProgress.Value = 0
        .FraMainProgress.Caption = "0% Completed"
        .LblMainProgressText.Caption = ""
        .Repaint
    End With
End Sub
 
Public Sub DeactivateProgressBar()
    Unload FrmProgressBar
End Sub

In the code of the progress userform, include this:
Code: 
Private Sub UserForm_Activate()
    Call ResetProgress
End Sub
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'---------------------------------------------------------------------
'- disable the closing of the userform by clicking on x in top right -corner 
'---------------------------------------------------------------------
    If CloseMode = vbFormControlMenu Then
        Cancel = True
    End If
End Sub

Then in your code, you need to track the progress yourself! This will not go automatically. When your long macro starts, show the progressbar with
Code: 
Call FrmProgressBar.Activate("My long macro...")
Every time your code progresses, you can update the progressbar and its message with:
Code: 
Call FrmProgressBar.SetProgress(10, "10% done, be patient...")
That 10 is just an example, you can put calculated stuff in there that takes into account some loopvalue, just make sure you use it as a percentage, so for example, if your loop goes from 1 to 10000, pass your loopcounter divided by 100 to the SetProgress sub.
When your long macro ends, just call the DeactivateProgressBar sub.
 
Upvote 0
Hermanito,

Thanks for the reply and the detailed instructions. I willplay with it later. The problem i have though is not implementing the progress bar itself, but rather allowing the long macro to run in the background while the progress bar is visible. I have learned that this is something tho do with the display mode of the progress bar being set to "Modal".


However when I set the userform to display as "Non-Modal", the graphics on my progress bar do not display, So if anyone has any idea's please drop a quick note here.

Many thanks again,

Lee.
 
Upvote 0
Hi Lee,

I always found John Walkenbachs method extremly good and easy to implement.
Make sure you include the DO EVENTS statement in your code - the first time I followed Johns instruction I overlooked that part and my progressbar didn't work...
 
Upvote 0
If you check my code, you'll notice a few Repaint statements. They make sure the form is shown and updated, even while other code is running, even when ScreenUpdating is set to False (which is what is the cause for your problem, i think).
And indeed, I forgot to mention, the progressbar userform has to be set to ShowModal=False.

Edit: just had a look at the link yytsunamiyy gave. My method is more or less the same, but better separated from the code that uses the progressbar. I have mine set apart as form and a module, easy to use in other excel-applications, while John's method is not so clearly divided and reusable (imho). I tried to apply the OO principle datahiding/encapsulation.
 
Last edited:
Upvote 0
Thanks guys,
I have lots to try now and am sure it will come right in the end. Thanks for taking time to help.

Will post my results in case they can help others.

Lee.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
populate message box from the second running the macro
2 3
Replies
23
Views
587
Mussa
M
B
  • Question
VBA Macro Not Detecting Slicer Correctly in Excel Workbook
Replies
0
Views
262
bigodudo
B
rjplante
  • Solved
Code following macro doesn't run
Replies
1
Views
249
rjplante
rjplante
C
  • Question
Running Data Refresh Macro, then Table Update Macro, Back-to-Back?
2
Replies
11
Views
525
Alex Blakenburg
A
J
  • Solved
Userform Code "Best Practice"
Replies
8
Views
547
jallum
J

Forum statistics

Threads
1,214,624
Messages
6,120,591
Members
448,973
Latest member
ksonnia

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top