MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Trying to use Worksheet_Change subroutine

  Asked By: Ashan    Date: Dec 22    Category: MS Office    Views: 767

I am trying to use Worksheet_Change event and having a few issues with it.

I have two pivots in a sheet, both pivots have the same source but hold
different data, what I am trying to do is if I change the page field of one
pivot then the page field of the second pivot should change automatically. I
succeded in doing that, what I did was I created a link to first pivots page
field in cell 'A9' and used the following code which updates the second pivot:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim mystr As String

If Intersect(Target, Range("a9")) Is Nothing Then

mystr = Range("a9").Value
ActiveSheet.PivotTables("PivotTable2").PivotFields("D").CurrentPage = mystr

End If

End Sub

The issue which I am facing is, the macro just keeps on refreshing the sheet on
and on. I guess it is because of Worksheet_Change event which runs with change
in each cell.

So I need help to figure out how can I :

1- Trigger a macro when value of a cell changes (part of which I have done)
2- Make this code more robust so that it quicly refreshes the pivot and does not
go on and on.
3- If any one could suggest me to use a different piece of code which would be
more efficient.



1 Answer Found

Answer #1    Answered By: Silvia Chapman     Answered On: Dec 22

I've come across this many times.
What I have finally done is go into the change  event
and after the "If Intersect" line, add:
Application.EnableEvents = False
then at the end  of the sub, add:
Application.EnableEvents = True

Of course, if you're doing things within the subroutine
that you WANT to use the event  handler, you'll have
to add the appropriate statements there...
For debugging purposose, I also created  the subs:
Sub Events_Disable
Sub Events_Enable
to use if the sub errors out while the events are disabled.
hope this helps

Didn't find what you were looking for? Find more on Trying to use Worksheet_Change subroutine Or get search suggestion and latest updates.