Combo box that controls pivot table

rainoutofblue

New Member
Joined
Nov 28, 2005
Messages
1
:rolleyes:
Hey there, I have a dataset that contains info regarding the name of sales rep, month as well as program names. I'm trying to come up with a program that will allow each sales rep to look at only info regarding him/herself, and allow them to look at one at a time the performance of each of their programs.

To do that, I get a list of the programs each sale rep is assigned to by doing a pivot table with sales rep name as a stand-alone drop-down menu on the top, and then tried to use a combo box to allow users to select the sales reps' name.
I compose the following VBA code, where C2 is the cell linked to the selections of the combo box:

Private Sub ComboBox1_Change()
Worksheets("SPName Pivot").PivotTables("PivotTable1").PivotFields("Name").CurrentPage = Worksheets("Data.Staging").Range("C2").Value
End Sub

This basically implies that the month selected in the pivot table will be the same as the one chosen from the drop-down menu of the combo box. Furthermore, I added the 2nd combo box which use the results of pivot table as their range of values.

Innitially, it works such that when I chose sales rep A, the pivot table change accordingly and the listed values in the 2nd combo box change to contain only the programs belonging to the selected sales rep.

However, when I try the selection a few times and then save the changes, somehow it is messed up, and I see some of mismatches between the name selected and the listed programs in both the pivot table and the 2nd combo box.

Have anyone here encounter similar situation before? I'd really love to get some insights as for how to fix this.

Thanks a lot,
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,489
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top