I have created a user form in VBA with the help of multiple online sources, to control a pivot table and subsequently a pivot chart. I understand this isn't the most elegant way to visualise this but I'm too far gone to amend it all now...
I have attached part of my code below. The rest of the code is duplicated for two more list boxes with some error handlers at the end. Note: I have the 'For w = 0 to 1' to loop through the code as otherwise it doesn't load & execute the form (otherwise the user must click the confirm button twice).
I'd really appreciate if someone could give me a hand speeding it up as it takes a good 5 seconds to run each time.
Thanks in advance!
Public Sub ConfirmButton1_Click()
On Error Resume Next
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For w = 0 To 1
'Check to see if any Car Filter criteria selected
'--------------------------------------------
X = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
X = X + 1
End If
Next
'Filter based on Car selection
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Car")
For i = 0 To Me.ListBox1.ListCount - 1
If X = 0 Then GoTo eh1
.PivotItems(i + 1).Visible = Me.ListBox1.Selected(i)
Next
End With