Hi there,

The following code allows me to contol a pivot table's page fields using a combobox when both the pivot table and the combo box are on the same page.

Please can you show me how to adapt the code so that I can have the combo box in a separate sheet to the pivot table.

Many thanks

Behind Pivot Sheet:

Option Explicit  
  
Private Sub ComboBox1_Change()  
  
If Me.ComboBox1.ListIndex < 0 Then  
  
Exit Sub  
  
End If  
  
Select Case LCase(Me.ComboBox1.Value)  
  
Case Is = "scenario 1"  
  
With Me.PivotTables("pivottable1")  
  
.PageFields("Name1").CurrentPage = "asdf1"  
  
.PageFields("name2").CurrentPage = "qwer1"  
  
End With  
  
Case Is = "scenario 2"  
  
With Me.PivotTables("pivottable1")  
  
.PageFields("Name1").CurrentPage = "asdf2"  
  
.PageFields("name2").CurrentPage = "qwer3"  
  
End With  
  
End Select  
  
End Sub

Behind ThisWorkbook module: 

Option Explicit  
  
Private Sub Workbook_Open()  
  
With Worksheets("Sheet2").ComboBox1  
  
.Clear  
  
.AddItem "Scenario 1"  
  
.AddItem "Scenario 2"  
  
End With  
  
End Sub