SetFocus on a pivot table

[timtimba]

Hi,

I have a drop down list (a simple list with unique values).

All I want to do is when a user selects this list, the focus moves to the cell within the pivot table where the value is matched.

Is this possible?

Thanks

 

[RoryA]

Is this a row/column/page/data field, or could it be any of those?

 

[timtimba]

Its row .....

at the moment I am just using conditinal formatting to hoghlight the column based on the drop down list above .... but I want to move to the cell within the sheet when the drop down is selected.

E.G.

list contains apple, pear, tomato

when apple is selected, the corresponding apple field within the pivot list is highlighted. however I want to move the focus to the apple cell too.

 

[RoryA]

You should be able to use code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim pt As PivotTable
   Dim MonitorCell As Range
   ' this is cell containing the list
   Set MonitorCell = Range("Q5")
   
   If Not Intersect(Target, MonitorCell) Is Nothing Then
      Set pt = ActiveSheet.PivotTables(1)
      pt.PivotSelect MonitorCell.Value, xlLabelOnly
   End If


End Sub

adjusting the ranges to suit your workbook.

 

[timtimba]

sorry a bit of noob here .... where do I place this code?

 

[RoryA]

Right-click the worksheet tab where the dropdown and pivot are, choose View Code, then paste that in.

 

[timtimba]

gets the following error:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim MonitorCell As Range
' this is cell containing the list
Set MonitorCell = Range("A1")

If Not Intersect(Target, MonitorCell) Is Nothing Then
Set pt = ActiveSheet.PivotTables(1)
pt.PivotSelect MonitorCell.Value, xlLabelOnly
End If

End Sub

 

[RoryA]

Can you put a sample file somewhere? That code worked in my (admittedly quick) tests using a row field.

 

[RoryA]

Based on the picture you messaged me, A1 bears no relation to any value you are searching for?