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,
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,118,033
Messages
5,569,777
Members
412,291
Latest member
marypolitan
Top