ActiveX Combo Box and Pivot Table using Visual Basic Issue

[JohnFWG]

I am currently working on a spreadsheet where I need to display a graph of some sales data.

The sales data that I am working with is in an excel table and that table is linked to a pivot table. From my pivot table I have a pivot chart that displays the number of number of member types for each type of member (Bronze, Silver, Gold, and Platinum). This member type data is displayed in the chart by each of the sales person names. Then I have a filter for the pivot table that allows the user to sort by a specific lead source. There are around 12 different lead sources and they are things like google, web, fliers, etc.

I only want to display one of these lead sources at a time as the graph becomes far to cluttered when you display multiple sources at one time. So, instead of using a slicer I have combo box that will allow a user to select one of the sources, and will only allow for one to be selected at a time.

I am using an ActiveX combo box and I have it linked to some visual basic code. However, I have been having an issue with this. Below you can see the error code that I am facing as well as the visual basic code that I am using.

I am using excel 2019.

Additionally, my table is not in the data model for use in power pivot. I do have the power pivot add in installed on my version of excel.

VBA Code:
Private Sub ComboBox1_Click()
Worksheets("SalesMix").PivotTables("SalesMixPivot").PivotFields("Lead Source").ClearAllFilters
Worksheets("SalesMix").PivotTables("SalesMixPivot").CurrentPage = Range("I3").Value
End Sub

Error message:
Run-time error '438':
Object doesn't support this property or method

When I use the debugger the second line is highlighted as the issue

For additional reference SalesMix is the name of the worksheet that contains the pivot table, and SalesMixPivot is the pivot table name. Lead Source is the filter that I am trying to sort by. Additionally my combo box is linked to the cell I3 on the sheet that the combo box is in. The sheet that the combo box and chart are on is separate from the SalesMix worksheet. It is on a separate tab names Dashboard

Any help would be appreciated, and I am happy to add more information if it is needed. I tried to be thorough in my description, but I understand if it is a little confusing. I am also happy to send anyone the worksheet that I am doing if that will help clarify any issues.

[大灰狼1976]

Hi JohnFWG!
Welcome to vbax forum.
Not sure.

Worksheets("SalesMix").PivotTables("SalesMixPivot").PivotFields("Lead Source").CurrentPage = Range("I3").Value

[Rob342]

Better to create a dynamic range to fill the combo box then select the index no when the user selects a choice
ActiveX controls works differently
also you might need to set the worksheet and use With and End With in the sub
Just my thoughts
Rob

[Bob Phillips]

Better to get rid of ActiveX controls completely, they are flaky and each new release of Windows offers less support.

[JohnFWG]

Hi JohnFWG!
Welcome to vbax forum.
Not sure.

Worksheets("SalesMix").PivotTables("SalesMixPivot").PivotFields("Lead Source").CurrentPage = Range("I3").Value

Thanks for the help it looks like that code fixed my issue and it all works as intended now.