JohnFWG
04-24-2019, 01:23 PM
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.
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.