how to use Change event

[PeaceMaker]

Hi All:

Could somebody 'splain to me how to use the Change event. More specifically,
I want to code the event to refresh my pivot table when the user chooses a
item from the pull-down menu located in the Page field of the pivot table.
So, assuming no one moves this menu, it is in cell A1. I'm thinking that I
can code the Change event so that when the user chooses an item from this
location, the Change even t will kick in and do what I need it to do. The
help is no help. I don't know how to get the cell range into the formula.

Anyway, any help would be appreciated.

thanx,

tod

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Target is the cell that has triggered the event
if Target.Address = "$A$1" then
'change occured in A1
Application.EnableEvents = False
Activesheet.PivotTables(1).Refresh
Application.EnableEvents = True
end if
End Sub

However don't be disappointed if this doesn't work. I don't know if
changing a pagefield value/item will trigger a change event or not.

Regards,
Tom Ogilvy
MVP Excel

PeaceMaker wrote in message <#ovaM5HX$GA.263@cppssbbsa05>...

[Robert Rosenberg]

Use the Worksheet_Calculate event to capture those changes.
_______________
Robert Rosenberg
RCOR Consulting
Microsoft MVP - Excel

http://ntware.com

PeaceMaker <tod...@flex.net> wrote in message
news:#ovaM5HX$GA.263@cppssbbsa05...

[Robert Rosenberg]

I thought it best to give you a bit more detail (I wanna be like Tom <g>),
so here's a routine I scraped together. I use a module level variable to
keep track of the last selection from the Page Field. This routine was place
in the Worksheet containing the PivotTable's code module...

'Not sure what your Pagefield consists of, so I'm using a Variant
Dim mvPivotPageValue As Variant

Private Sub Worksheet_Calculate()

Dim pvt As PivotTable

Set pvt = Me.PivotTables("PivotTable1")
If LCase(pvt.PivotFields("Division").CurrentPage) <>
LCase(gvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
gvPivotPageValue = pvt.PivotFields("Division").CurrentPage
Application.EnableEvents = True
End If

End Sub

Note: It will not fire if the user chooses the current Page field item from
the dropdown.