Please check where I have gone wrong Private Sub Worksheet_Change(ByVal Target As Range)

[PanzerAttack]

Hi been a while since I've posted and I seem to have forgetten so much.

I have a sheet (Selection) where I want my users to select from a pivot table (Cell K13) a users name. When they do so, then the same name is selected in all the other pivot tables in the workbook. As a snapshot, this is what I have. But nothing is happening, what have I left out?


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim OwnerSelection
Let OwnerSelection = ActiveSheet.Range("K13")

If Target.Address = "$K$13" Then

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

Application.EnableEvents = True
Application.ScreenUpdating = True

End If

End Sub

Please help, I'm at a loss.

 

[par60056]

I don't do much with event routines but I'll give it a shot.

Can you set a breakpoint in the routine? I suspect that the target range being sent in is the entire pivot table not just K13 (the cell the user changed).

 

[PanzerAttack]

That sounds very likely, what is a breakpoint sorry.

 

[PanzerAttack]

Hi is anyone able to advise on this point for me?

 

[PanzerAttack]

I've changed to:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim OwnerSelection
Let OwnerSelection = Sheets("Selection").Range("K13")

If Target.Address = "$K$13" Then

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


But still nothing.

 

[PanzerAttack]

It Appears to be this bit it's not happy with

If Target.Address = "$K$13" Then

I've tried Cell & Value, is there something else I need to try? I'm googling like made.

 

[PanzerAttack]

Latest code, I can't seem to get it to recognise the range


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = True

Dim OwnerSelection
Let OwnerSelection = Sheets("Selection").Range("$K$13")

If Target.Address = "$K$13" Then

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

Sheets("Selection").Select

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[PanzerAttack]

Okay sorry to keep bouncing this but I've been at it all day and I can see no reason for it

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OwnerSelection
Let OwnerSelection = Sheets("Selection").Range("$K$13")

Application.ScreenUpdating = False

'On Error Resume Next

If Target.Address = "$K$13" Then

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

Sheets("Selection").Select

Else

Sheets("Selection").Select

End If

Application.ScreenUpdating = True


Can anyone please see why it wont recognise the Range. I'm using Excel 2010 if that makes a difference.

 

[par60056]

Sorry. Different time zones and I just got to work.

When you are in the editor, if you click in the grey area left of the line of code, that will set a breakpoint. When the code executes it will stop there. That will give you the opportunity to examine what the value of Target.Address is. But breakpoints in event routines can cause problems (particularly in _selection events)

Try adding a msgbox to the code to display the value of target.address.

BTW, you should not need to change the active page. Event procedures need to be careful in what they change.

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

Sheets("Selection").Select

could be:

Sheets("AccountNumber").PivotTables("PivotTable1").PivotFields("Owner").ClearAllFilters
Sheets("AccountNumber").PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

 

[Jonmo1]

Something tells me that changing the selections in a pivot table does not qualify as a "Change" of sheet values.

There are event procedures dedicated to Pivot Tables
Such as
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

In the Sheet Module, look in the dropdown menu on the top right for a list of all the events available for your use.