Add a Progress bar to a user form

[MarkCBB]

HI there VBA Guru's

I am using the following code to filter some Pivot Tables on a worksheet, however it takes sometime to run the marco, I would like to have a progress bar in the user form to show the progress of the macro. however I have not been able to insert a progress bar into the user form.

Below is the code that I am using:

Private Sub CommandButton1_Click()
Dim MyRegion As String
Dim i As Integer
Dim P_count As Integer

P_count = ActiveSheet.PivotTables.Count
i = 1
Range("A2").Value = ListBox1.Value
MyRegion = Range("A2").Text
Do
    With Sheets("Report").PivotTables("PivotTable" & i).PivotFields("Client Region")
        .PivotItems(MyRegion).Visible = True
        For Each Pi In .PivotItems
           If Pi.Name <> MyRegion Then Pi.Visible = False
         Next Pi
    End With
    i = i + 1
Loop Until i = P_count + 1
End Sub

Private Sub UserForm_Initialize()
With ListBox1
    .AddItem "Central"
    .AddItem "Eastern Cape"
    .AddItem "Kwa-Zulu Natal"
    .AddItem "Limpopo"
    .AddItem "Mpumalanga"
    .AddItem "Northern Gauteng"
    .AddItem "Southern Gauteng"
    .AddItem "Western Cape"
End With
End Sub

 

[Weaver]

Adding a progress bar to a user form isn't too much of a problem in itself. Right click the controls toolbox and choose 'additional controls'. You need to select 'microsoft progressbar v6.0' from the list.

Where you may experience problems is how you update it. If you're in a control loop of vb code you can achieve this more easily, but if your process is dependant on inbuilt functions and it's these that are taking the time then updating the progress bar in your code to show different levels of completion wont' be possible.

 

[MarkCBB]

Hi there Weaver,

thanks for your help, I got it working, looks pretty cool. thank you. below is the updated code:

Private Sub CommandButton1_Click()
Dim MyRegion As String
Dim i As Integer
Dim P_count As Integer
Dim q As Integer
Dim P_Loop As Integer
Dim x As Integer

P_count = ActiveSheet.PivotTables.Count
i = 1
Range("A2").Value = ListBox1.Value
MyRegion = Range("A2").Text
ProgressBar1.Visible = True
q = 0
x = 1
P_Loop = P_count * Sheets("Report").PivotTables("PivotTable" & i).PivotFields("Client Region").PivotItems.Count

Do
    With Sheets("Report").PivotTables("PivotTable" & i).PivotFields("Client Region")
        .PivotItems(MyRegion).Visible = True
        For Each Pi In .PivotItems
           If Pi.Name <> MyRegion Then Pi.Visible = False
           
           q = x / P_Loop * 100
           ProgressBar1.Value = q
           x = x + 1
         Next Pi
    End With
    i = i + 1
Loop Until i = P_count + 1
q = x / P_Loop * 100
End Sub
Private Sub UserForm_Initialize()
With ListBox1
    .AddItem "Central"
    .AddItem "Eastern Cape"
    .AddItem "Kwa-Zulu Natal"
    .AddItem "Limpopo"
    .AddItem "Mpumalanga"
    .AddItem "Northern Gauteng"
    .AddItem "Southern Gauteng"
    .AddItem "Western Cape"
End With
End Sub

 

[MarkCBB]

Quick question, how can I make the form unload it sleft after the command button is pushed?

 

[nightcrawler23]

Try unload commnad as below

Hi there Weaver,

thanks for your help, I got it working, looks pretty cool. thank you. below is the updated code:

Private Sub CommandButton1_Click()
Dim MyRegion As String
Dim i As Integer
Dim P_count As Integer
Dim q As Integer
Dim P_Loop As Integer
Dim x As Integer

P_count = ActiveSheet.PivotTables.Count
i = 1
Range("A2").Value = ListBox1.Value
MyRegion = Range("A2").Text
ProgressBar1.Visible = True
q = 0
x = 1
P_Loop = P_count * Sheets("Report").PivotTables("PivotTable" & i).PivotFields("Client Region").PivotItems.Count

Do
    With Sheets("Report").PivotTables("PivotTable" & i).PivotFields("Client Region")
        .PivotItems(MyRegion).Visible = True
        For Each Pi In .PivotItems
           If Pi.Name <> MyRegion Then Pi.Visible = False
           
           q = x / P_Loop * 100
           ProgressBar1.Value = q
           x = x + 1
         Next Pi
    End With
    i = i + 1
Loop Until i = P_count + 1
q = x / P_Loop * 100
End Sub
Private Sub UserForm_Initialize()
With ListBox1
    .AddItem "Central"
    .AddItem "Eastern Cape"
    .AddItem "Kwa-Zulu Natal"
    .AddItem "Limpopo"
    .AddItem "Mpumalanga"
    .AddItem "Northern Gauteng"
    .AddItem "Southern Gauteng"
    .AddItem "Western Cape"
End With
[COLOR="Red"]UNLOAD Me[/COLOR]
End Sub

 

[Weaver]

if you use

me.hide

before you turn off screenupdating, the form won't be visible

then use

unload me

after all your code is run. Nightcrawler has it in the 'initialise' event handler, which I'd expect to be problematic.

 

[MarkCBB]

Thanks guys,

hahah I was trying "unload.me"

thank you for your help. I have it all working now.

 

[Weaver]

Thanks guys,

hahah I was trying "unload.me"

thank you for your help. I have it all working now.

When I first came across this, I guessed incorrectly that it would be me.unload