VBA: Get a pivot table to filter automatically based on list choice in Active X Combo Box

Hi all,

I am a complete beginner with VBA so am sure it's a simple step I am missing, but would appreciate any help you can offer.

I have a spreadsheet with a ComboBox in cell C3. It's an ActiveX combo box, which allows me to start typing in any part of an item's description, and it will show a drop down list of item descriptions which contain the text I have already started to type.

On the same spreadsheet, I have a pivot table of data, and I'd like the pivot table to update based on the item selected in the ComboBox in cell 3.

Through recording a macro (called PivotFilter) of me manually filtering the pivot, and then editing the code slightly to point at cell C3, I have a macro that successfully updates the pivot when I run it manually.

Please could someone help me to get the macro to run automatically when the user clicks their item description from the drop down list in the ComboBox, or pastes in an item description into the ComboBox?

Currently I have this code, which is just the 2 subroutines together. Each works individually, but when I put all the code together like this, I can still start typing in the combo box and choose from a drop down list but the filtering doesn't happen.


Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown

End Sub


Sub PivotFilter()
'
' PivotFilter Macro
'

'
ActiveSheet.PivotTables("PivotTable5").PivotFields("DESCRIPTION"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields("DESCRIPTION").CurrentPage _
= Range("C3").Text
Range("C4").Select
End Sub


thanks in advance

Charlotte

Perhaps:

Please Login or Register  to view this content.