MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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