VBA query: how to assign 2 Subroutines to an 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 instructions on how to handle this..


    I have a worksheet with a ComboBox in cell C3. It's an ActiveX combo box, which (thanks to VBA code) 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. (the source list is on another worksheet).


    On the same worksheet, 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 C3.


    Through recording a macro (called PivotFilter) of me manually filtering the pivot table, 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.


    The aim is for the pivot table to filter automatically on the chosen item description 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. 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.



    thanks in advance


    Charlotte

  • Re: VBA query: how to assign 2 Subroutines to an Active X combo box


    Try this


    Code
    Private Sub ComboBox1_Change()
        ComboBox1.ListFillRange = "DropDownList"
        Me.ComboBox1.DropDown
        ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    End Sub
  • Re: VBA query: how to assign 2 Subroutines to an Active X combo box


    Thank you for the response.


    Unfortunately this doesn't seem to work. I am a novice with VBA so could be missing something, but in the code you suggested, there appears to be no command to tell the code to update the pivot table based on the item description selected in cell C3.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!