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
