What you need to do with that progress bar is change the width of the 'BarColor' label to indicate how far it's gotten. If you have a numbered loop anywhere in your macro (for example a FOR NEXT loop that counts down rows of data) then you simply divide the max width of the label (264 in this case) by the total number of rows and then multiply it by the row number you're on.
For example, in my code:
'########## This first section initialises the progress bar
With ProgressBar
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show vbModeless
.ProBar.Width = 1 '############# this is what my coloured label is called, yours is called 'BarColor'
.StatusBar.Caption = "Getting performance (1 / " & UBound(RepList, 2) + 1 & ")"
.Repaint
End With
And every time the loop cycles:
ProgressBar.ProBar.Width = (630 / (UBound(RepList, 2) + 1)) * Sweep + 1
'################# My Progress bar has a max width of 630. Therefore I divide 630 by the total number of items I'm processing (Ubound(RepList, 2)) and then multiply it by the loop counter (Sweep). Oh and my loop counter starts at 0, not 1, so I add 1 to it.
ProgressBar.StatusBar.Caption = "Getting performance (" & Sweep + 1 & " / " & UBound(RepList, 2) + 1 & ")"
ProgressBar.Repaint
DoEvents '######### You need the 'DoEvents' command or the progress bar stops updating mid way through
Next Sweep
If you don't have a numbered loop you can use then you'll just have to put hard coded width updates at various points in your code.
Bookmarks