Solved: Hyperlink pivot pagefield control - not working

[Onetrack]

Hello All,

I am trying to incorporate a hyperlink rollover change to control pivot pagefields.

I have posted for help at MrExcel but no luck so far.http://www.mrexcel.com/forum/excel-questions/691788-hyperlink-pivot-pagefield-control.html

I have posted a sample file here:

https://www.box.com/s/dg0atnuidb4zjxsf9vgz

any assistance would be very much appreciated.

[SamT]

Hyperlinks, Shapes and Pivot Tables have no Events to trigger something.

The only object that has a mouse type event is a chart.

However, the Worksheet Object has a selection change event with a Target (Range). Ranges have Pivot properties that you can check and IF the Pivot property is there, THEN run the sub ELSE " Exit Sub."
If Not (Property Test) Then Exit Sub
This, however, means that you would have to actually click on some object to trigger the sub.
HTH

I opened the first example book you gave MrExcel and changed the Worksheet_Change to Worksheet_ SelectionChange and it triggers the sub, but Without input arguments it doesn't do anything

[Aflatoon]

Try changing the event code to:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim critRange As Range
Dim pi As PivotItem
Dim i As Long
Dim strFields() As String

Set critRange = Range("C1:C2")
If Intersect(Target, critRange) Is Nothing Then GoTo CleanUp

strFields = Split("Shop;Week", ";")
On Error GoTo CleanUp

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

With Worksheets("Sheet1").PivotTables("PivotTable1")
.ManualUpdate = True
For i = 1 To critRange.Rows.Count
With .PageFields(strFields(i - 1))
.ClearAllFilters
For Each pi In .PivotItems
Debug.Print pi.Value & " " & Now() & "; criteria " & critRange(i).Value
If pi.Value = critRange(i).Value Then
.DataRange.Value = critRange(i).Value
Exit For
Else
.DataRange.Value = "(All)"
End If
Next pi
End With
Next i


Application.EnableEvents = True
.ManualUpdate = False
.RefreshTable
End With

CleanUp:

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub

[Onetrack]

SamT - thanks that is very useful indeed.

Aflatoon - awesome! The .DataRange.Value seems to have fixed it!

SamT - do you have any thoughts about Aflatoon's solution?

cheers, JohnS.

[SamT]

If I knew anything at all about Pivot Tables, I would have written his code exactly the same.

The only thing I might have done different is replace the Split Statement with a Range reference. But, I don't really know if that is possible.