XL 2002 Change Event 2

[vaneagle]

HI All,

Not sure how to go about this. I have a pivot table in a worksheet. IF a user selects a page selection I need an an event to happen. The cell that this sits in is B4.

The code I have is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.ActiveSheet.PivotTables("PivotTable9").PivotFields("Trading Dept").CurrentPage _
        = "Total"
End Sub

Perhaps I think I am not using this correctly. Basically if the User changes cell B4 to the option "All", then I want the code to correct their selection so that "Total" instead is selected. If they choose anything else apart from "All" then nothing happens with the code. Does this make sense?

thanks in advance,

vaneagle

 

[PHV]

Take a look at the Intersect method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

The problem is that the TARGET when a pivottable is changed is not necessarily the page field - I think you get a range of cells that covers the entire pivot table - as PHV says - look into the intersect method - you can also test what "Target" is by using

msgbox Target.address

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[vaneagle]

Thanks Guys...

I'll do a search on the intersect method and test the 'Target' at the same time with your suggestion Geoff.

Thanks,
vaneagle

 

[vaneagle]

I got it to work with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets(3).Activate
    Set isect = Application.Intersect(Range("b4"), Range("b4"))
    If isect Is Nothing Then
        MsgBox "Ranges do not intersect"
    Else
        If ActiveSheet.PivotTables("PivotTable9").PivotFields("Trad Dept").CurrentPage = "(All)" Then
        
        ActiveSheet.PivotTables("PivotTable9").PivotFields("Trad Dept").CurrentPage = "Total"
MsgBox Target.Address        
        End If
    End If
  End Sub

thanks again for your suggestions..
btw with

msgbox target address

it indeed selects the entire pivot table.

Have a star guys!

regards,

vaneagle