VBA Selection Form Combo box to get list from Pivot table

[LauraWork]

Hi all

this may not be possible, I have searched threads but can't find my exact problem.

I have a pivot table with 'year' and 'name'.

I want to then use a selection form for a user to pick a year and name and then the pivot table will only show that data and then go on to run another macro that creates a report from that new pivot table selection.

I have created my form with combo boxes ready but how do I get them to pull the data from the pivot?

thanks

 

[LauraWork]

***Update***

I have populated the combo boxes however the 'selectors' have stopped working:

Private Sub UserForm_Initialize()


' *** populates the drop down lists




    Dim sheet As Worksheet
    Dim pt As PivotTable


    
    Set sheet = ThisWorkbook.Worksheets("SM Summary inc call stats - TSM")
    Set pt = sheet.PivotTables("TSM_Reporting")
    
    Dim ptField As PivotField
    
    
  
   
                Set ptField = pt.PivotFields("Fiscal Year")
                Dim item As PivotItem
                Dim index As Integer
                index = 1
                For Each item In ptField.PivotItems
                    Me.ComboBox1.AddItem item.Name
                Next item


                Me.ComboBox1.AddItem "(All)"
    


    
    Set ptField = Nothing
    
    
    
     


                Set ptField = pt.PivotFields("Fiscal Month")


                For Each item In ptField.PivotItems
                    Me.ComboBox2.AddItem item.Name
                Next item


                Me.ComboBox2.AddItem "(All)"
    
    Set ptField = Nothing
    
    
  
     


                Set ptField = pt.PivotFields("TSM")


                For Each item In ptField.PivotItems
                    Me.ComboBox3.AddItem item.Name
                Next item


                Me.ComboBox3.AddItem "(All)"
    
    
End Sub


Private Sub ComboBox1_Change()
' ** combo box 1 for year selector
    Dim sheet As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField


    Set sheet = ThisWorkbook.Worksheets("SM Summary inc call stats - TSM")
    Set pt = sheet.PivotTables("TSM_Reporting")
    Set ptField = pt.PivotFields("Fiscal Year")


    ptField.CurrentPage = Me.ComboBox1.Value


 Set ptField = Nothing




    Set ptField = pt.PivotFields("Fiscal Month")


    ptField.CurrentPage = Me.ComboBox2.Value


 Set ptField = Nothing






    Set ptField = pt.PivotFields("TSM")


    ptField.CurrentPage = Me.ComboBox3.Value
    
End Sub

 

[Jerry Sullivan]

***Update***

I have populated the combo boxes however the 'selectors' have stopped working:

Hi Laura,

Not sure exactly what you mean by "selectors have stopped working".

You only posted the ComboBox1_Change code. You'll need to have similar procedures for the other 2 ComboBoxes. If you don't have those, then that might be why ComboBox2 & 3 appeared to not work.

Your code worked for me provided I changed ComboBox2 & 3 first, before changing ComboBox1 and triggering the update.
You'll need an approach to avoid the scenario that the code attempts to update all 3 fields before all 3 selections are made.
Options include requiring all 3 selections before doing the update, or having each ComboBox only update its corresponding field.

 

[LauraWork]

Hi Jerry

thanks, you discovered what I meant, as soon as you change the first one it tries to change and gets stuck.

I thought I had put the code needed for all 3 comboBox changes under the section 'Sub ComboBox1_Change()' but I Just didn't change the name.
So where have I gone wrong?

 

[Jerry Sullivan]

Hi Jerry
I thought I had put the code needed for all 3 comboBox changes under the section 'Sub ComboBox1_Change()' but I Just didn't change the name.
So where have I gone wrong?

That won't work, because if a selection is made to ComboBox1 first, then Sub ComboBox1_Change code will try to change the ComboBox2 and ComboBox3 when no values have been set for those.

Your main options...

You'll need an approach to avoid the scenario that the code attempts to update all 3 fields before all 3 selections are made.
Options include requiring all 3 selections before doing the update, or having each ComboBox only update its corresponding field.