MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to run under change/select condition in the same sheet


Posted by Nona Sloven on May 22, 2001 3:13 PM

Hello:

Can anybody help me out with this?

I have the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim WatchRange As Range

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$8" And _
Target = "Valores Incorrectos >>" Then Exit Sub

On Error Resume Next
Set WatchRange = Range("C6:I17")

If Not Intersect(Target, WatchRange) Is Nothing Then
Run "Refresh"
End If
Set WatchRange = Nothing

End Sub

This macro doesn't work if I select an item in cell C17 or in cell F17 which are dropdown lists for a pivot table. I have my pivot table in the same sheet. The macro was at first intended to work when a cell is changed in range C6:I17, but I need it to also work when an item in cells C17 or F17 is selected. Is this possible? Thank you.

Nona


Posted by Dave Hawley on May 22, 2001 5:40 PM


Hi Nona

The changing of a Pivot Table page Field will not trigger the Change Event, we can work around this though. In any cell(s) put a simple formula like =C6. You can hide the Column or Row of the cell if you want.

In the example below I have assumed you have formulas in range A1:A5 that are refering to the Pivot Table page fields. Change these to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim WatchRange As Range

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$8" And _
Target = "Valores Incorrectos >>" Then Exit Sub

On Error Resume Next
Set WatchRange = Range("C6:I17")

If Not Intersect(Target, WatchRange) Is Nothing Then
Run "Refresh"
End If

Set WatchRange = Range("A1:A5")

If Not Intersect(Target, WatchRange) Is Nothing Then
Run "Refresh"
End If

Dave
OzGrid Business Applications

Posted by Dave, the code doesn't seem to run my macro except by change event on May 23, 2001 8:06 AM

Dave:

I tried with your code and the macro simply doesn't work. Maybe there is some detail we are not taking into consideration.

Nona