Any way to create a progress bar for my macro?

H

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
I have a macro that generates a report, and it takes a minute or two to execute. Is there any way I can create a progress bar to show the user how close it is to being done? I googled it, but didn't come up with anything that I could figure out how to apply to my code. My macro is this:

Code: 
Private Sub MyMacro()
Dim R As Long
Dim S As Long
Dim T As Long
S = 10
R = 11
T = 18
Sheets("Optics Report").Activate
Do While (S < 71)
    ActiveSheet.Range("C" & S & ":C" & (S - 1)).Value = Sheets("Image Intensifying Optics").Range("C" & (T) & ":C" & (T - 1)).Value
    ActiveSheet.Range("E" & S & ":E" & (S - 1)).Value = Sheets("Image Intensifying Optics").Range("E" & (T) & ":E" & (T - 1)).Value
    ActiveSheet.Range("M" & S).Value = Sheets("Image Intensifying Optics").Range("M" & (T)).Value
    ActiveSheet.Range("P" & (S - 1) & ":P" & (S + 2)).Value = Sheets("Image Intensifying Optics").Range("P" & (T - 1) & ":P" & (T + 2)).Value
    ActiveSheet.Range("Q" & (S - 1) & ":T" & (S + 2)).Value = Sheets("Image Intensifying Optics").Range("Q" & (T - 1) & ":T" & (T + 2)).Value
    S = S + 4
    T = T + 4
    Loop
 
 
Do While (R < 73)
    If ActiveSheet.Range("Q" & R) = "" Then
    Sheets("Optics Report").Range("M" & R & ":M" & (R + 1)).EntireRow.Hidden = True
    Else
    Sheets("Optics Report").Range("M" & R & ":M" & (R + 1)).EntireRow.Hidden = False
    End If
R = R + 4
Loop
 
R = 10
T = 1
Do While (R < 71)
    If ActiveSheet.Range("M" & R) = "" Then
    Sheets("Optics Report").Range("C" & R & ":C" & (R - 1)).EntireRow.Hidden = True
    Else
    Sheets("Optics Report").Range("C" & R & ":C" & (R - 1)).EntireRow.Hidden = False
    Sheets("Optics Report").Range("A" & R & ":A" & (R - 1)).Value = T
    T = T + 1
    End If
R = R + 4
Loop


It runs each of those loops 6 times with diffferent values for the variables and different arguments within the do while loops.

Thanks!

Hank
 
Sort by date Sort by votes
Thanks for the example, this is quite informative. I've created the user form and completed all the steps to make it look like a progress bar. I've started then next steps, but I have a couple questions.

In this example, the Subroutine in the code for the userform is calling Main. Am I supposed to create the subroutine Main? Or do I alter the code of MyMacro (the macro I want the progress bar to apply to) to include the increasing of the progress bar? It would make sense to me to create the user form, then insert code into MyMacro that would increase the progress bar as MyMacro progresses, but that doesn't seem to be what is happening in this example. Thanks again for the link. Any clarification would be greatly appreciated.

Hank
 
Upvote 0
In your macro you would need to call the initialise routine to display the form then in your loops use code like

Code: 
PctDone = Counter / (RowMax * ColMax)
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
'       The DoEvents statement is responsible for the form updating
        DoEvents

to update the form. Obviously you'll need to modify that code to give your actual percent done.
 
Upvote 0
Thanks that helps out a lot. Sorry for all the questions, I'm not all that good at this stuff and this is a lot to tackle at once. My last question before plugging away for a while again:

What is DoEvents? Is it a Sub? It doesn't say anything on that example about it besides that it is responsible for the form updating. Do I need to write the Sub DoEvents?

Thanks again.

Hank
 
Upvote 0
OK, so here's what I'm thinking. Right now, when the button is clicked that activates MyMacro, it actually calls the "GenerateReport" Sub. This is because I wanted to password protect this macro. So, the GenerateReport Sub just prompts for a password, and if the user enters the correct password it runs MyMacro. Could I just have it Call ShowDialog, then have ShowDialog call MyMacro and write code into MyMacro that updates the userform?

I feel like this is much simpler than what is being described in this example. Would this not work?
 
Upvote 0
Would either one make it more or less efficient? My macro is already a bit slow, so I want to bog it down as little as possible.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
234
Skyybot
Skyybot
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
173
Fluff
Fluff
R
  • Solved
Generate 1000 Iterations of a Calculated Value
Replies
4
Views
450
Peter_SSs
Peter_SSs
P
  • Question Question
How to find till which row the marco executed. It stopped at some random row without any error and unable to identitfy at what row it has been stopped
Replies
3
Views
239
FormR
FormR
Balcomie
  • Solved
Formula For Gematria
Replies
3
Views
241
StephenCrump
S

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