Run macro when any of the cells in range...
Posted by Nona Sloven on April 26, 2001 2:05 PM
Please help me with this:
I have a pivot table in Excel'97. In a number of rows above the pivot table I have a range D7:I9. I want to run a macro I have named "Refresh" automatically when any of the cells in the range mentioned changes. Please help.
Nona.
Posted by Kevin James on April 26, 2001 5:21 PM
Nona:
I have belayed replying because I figured someone with more knowledge of this would have replied by now.
As a high-level logic, rewrite your VBA to run when the file is open. Have it continually monitor for changes in the cells you mentioned. then a refresh can take place.
Sorry, I am still in VBA infancy.
-Kevin.
Posted by Steve W on April 26, 2001 8:32 PM
HI NONA
Try this right click on your page with the range and paste in the following.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
Dim Cl As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("D7:I9")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.Run "'My Program.a.xls'!Macro1"
End If
Set WatchRange = Nothing
'End If
End Sub
Change name and macro name to your names.
Steve
Posted by Dave Hawley on April 26, 2001 10:32 PM
No objections, but.........
Steve, I believe I have helped you in the past many times! While I have no objection to others using my code or using it to help others, I do think you should at least acknowlege that it is my code.
Nona, my code that Steve suggested to you should be changed to the following.
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 IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("D7:I9")
If Not Intersect(Target, WatchRange) Is Nothing Then
Run "Refresh"
End If
Set WatchRange = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Steve W on April 27, 2001 6:14 AM
Re: No objections, but.........(Sorry Dave I meant No disrespect to you, I'll make sure to remember to do this in the future)
Posted by Nona Sloven on April 27, 2001 9:01 AM
Re: No objections, but.........
Hi Dave:
I feel confortable with your reply. It worked ok. Could you please now add an additional code to your coding so that my macro "Refresh" doesn't run when the cell B9 contains the phrase "Incorrect Values >>". Thank you in advance.
Nona
Posted by Nona Sloven on April 27, 2001 9:06 AM
Hi Dave:
I feel confortable with your reply. It worked ok.
I keep fresh in mind that you helped me in the past too. Could you please now add an additional code to your coding so that my macro "Refresh" doesn't run when the cell B9 contains the phrase "Incorrect Values >>". Thank you in advance.
Nona
Pd: I´d also want to express my thanks to Kevin for his cooperation.
Nona: I have belayed replying because I figured someone with more knowledge of this would have replied by now. As a high-level logic, rewrite your VBA to run when the file is open. Have it continually monitor for changes in the cells you mentioned. then a refresh can take place. Sorry, I am still in VBA infancy. -Kevin.
Posted by Dave Hawley on April 27, 2001 10:04 AM
Re: No objections, but.........
I feel confortable with your reply. It worked ok. Could you please now add an additional code to your coding so that my macro "Refresh" doesn't run when the cell B9 contains the phrase "Incorrect Values >>". Thank you in advance. Nona
Nona, just add another Exit:
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 IsEmpty(Target) Then Exit Sub
If Target.Address = "$B$9" And _
Target = "Incorrect Values" Then Exit Sub
On Error Resume Next
Set WatchRange = Range("D7:I9")
If Not Intersect(Target, WatchRange) Is Nothing Then
Run "Refresh"
End If
Set WatchRange = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Nona Sloven on April 27, 2001 11:06 AM
Re: No objections, but.........
Dave:
Thank you for your valuable help! I just one more time want to know how to get my macro "Refresh" also to run when I clear an entry in range D7:I9 and not only when I enter data on these cells.
Thank you a lot!
Nona : Hi Dave
If Not Intersect(Target, WatchRange) Is Nothing Then
Posted by Dave Hawley on April 28, 2001 7:12 AM
Re: No objections, but.........
Thank you for your valuable help! I just one more time want to know how to get my macro "Refresh" also to run when I clear an entry in range D7:I9 and not only when I enter data on these cells. Thank you a lot! Nona
Nona, remove:
If IsEmpty(Target) Then Exit Sub
Dave
OzGrid Business Applications
