Progress Bar Linked To Macro

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
I want to use this progress bar code. Link however I am not 100% sure how to link it back to my macro , so that the progress reflects completion of my macro. The LENGTH of the task will vary depending on what the user has input. I had another progress bar, but that would reach 100% way before the code had complete the task. I can only assume I had not linked it correctly to my code.

Could someone please have a look at the above link and advise WHERE in the code I need it to track the progress of my code that is running. As the LENGTH of the task will vary, I assume somewhere in the taskbar code this option will need to keep changing. I could try to set the code to get that bit of data from a sheet cell reference. If this is the case can someone please advise what part in need to change.

Hope that makes sense.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

nardagus

Active Member
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
This piece of code is responsible for progress bar to work:
progress = progress + inc

I don't know what you are trying to do with your macro. If it comes to LOOPs:
maxProgress is the amount of all loop passes.
progress is each loop pass.
So if you need to analyze 100 cells with a loop then:
VBA Code:
for inc = 1 to 100
    do stuff
progress = progress + inc
next i

where maxProgress = 100
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
Nardagus

Thanks for the update, My code will work on a loop, the user can decide how many loops they want to do, I can get that data added to a sheet, e.g. sheet1 cell a1 and the above code can get the amount of times it need to loop from there.

Looking at your code, it does not match anything on the code in the link, is it this bit in the code?
Also is this the only bit I need to change?
VBA Code:
'Use the ProgressBar to track macro execution by running the For loop 100 times
For i = 0 to 99 
    pb.AddProgress 1 'Add 1% progress
    '...
Next i
 

nardagus

Active Member
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Yes. The loop you posted is responsible for running progressbar.
depending on the size of loop you need to change two things

VBA Code:
pb.Initialize "My title", 200

and

VBA Code:
For i = 0 To 200
    pb.AddProgress 1 'Add 1% progress
    '...
Next i
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

super, I will give this a shot. See what happens
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
I have added the progress bar straight to my Userform, rather than create a new userform for the progress bar. This is how it looks now. I have also added the main bulk of the code to my userform and made NO changes to it. In addition I have made a few changes to the original code see below to reflect my needs.
1589025847363.png

From
VBA Code:
Sub ExampleProgressBar()
    Dim pb As ProgressBar
         Set pb = New ProgressBar
               pb.Initialize "My title", 100
    'Add 10% progress
          pb.AddProgress 10
    '...
    'Hide and remove the Progress Bar
         pb.Hide
    Set pb = Nothing
End Sub

To - I have put this code into MY user-form Initialise, as it did not state where to put it.
VBA Code:
Private Sub UserForm_Initialize()
    Dim pb As UserForm1
        Set pb = UserForm1
          pb.Initialize "My title", Sheets("Sheet10").Range("Q1") ' Number of Loops is taken from Sheet10 Cell Q1
    'Add 1% progress
        pb.AddProgress 1 '1%
    Set pb = Nothing
End Sub

Problem, nothing happens. The progress bar is NOT tracking the progress.

Can someone please advise
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
I have just noticed that this is missing from the original code that I copied, It is not in the first version of the code but is in the second at the bottom.

VBA Code:
    For i = 0 To 99
    pb.AddProgress 1 'Add 1% progress
    '...
Next i

I have changed it to this
VBA Code:
    For i = 0 To Sheets("Sheet10").Range("Q1") ' Number of Loops is taken from Sheet10 Cell Q1
    pb.AddProgress 1 'Add 1% progress
    '...
Next i

So now it looks like this, but I am not sure if this code is in the right place
VBA Code:
Private Sub UserForm_Initialize()
    Dim pb As UserForm1
        Set pb = UserForm1
          pb.Initialize "My title", Sheets("Sheet10").Range("Q1") ' Number of Loops is taken from Sheet10 Cell Q1
 'Add 1% progress
      For i = 0 To Sheets("Sheet10").Range("Q1") ' Number of Loops is taken from Sheet10 Cell Q1
         pb.AddProgress 1 'Add 1% progress
Next i
    Set pb = Nothing
End Sub
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
Can anyone please help on this one as I am still stuck.
Currently my progress bar is on my userform and not a separate userform that is called. Thant is one of my issues.
Second issue is I am not 100% sure where to put this part of the code, see above for more details.

Thirdly I did create a separate userform and place the progress bar on that as a test, however I keep getting an error on this line. Even when I used the ORIGINAL CODE
My version
VBA Code:
pb.Initialize "My title", Sheets("Sheet10").Range("Q1") ' Number of Loops is taken from Sheet10 Cell Q1

Original version
VBA Code:
 pb.Initialize "My title", 100
 

Watch MrExcel Video

Forum statistics

Threads
1,128,077
Messages
5,628,507
Members
416,323
Latest member
65563

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
Top