Want to run macro only when specific cell changes

blentz2

New Member
Joined
Apr 3, 2002
Messages
3
I have a large macro that I only what to run when a specific cell has changed. This cell G4 is a date. When the used changes it, I want to run the macro which will change about 100 cell formulas to reflect the new date. Again only want to run macro when this particlar cell changes.
This message was edited by blentz2 on 2002-04-04 06:22
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You can do this with the WorkSheet Change Event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then YourMacro
End Sub
 
Upvote 0
Hi blentz2

Right click your sheet tab, left click View Code and paste this code in the white area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run "my macro name"
End Sub

change "my macro name" to the actual name of the macro you want to run

Hope this helps
regards
Derek
 
Upvote 0
This test code will add a blank row below the trigger cell A1 if any change is made to A1. You can modify this code to do what you want. This sub is added to the sheet module. JSW

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Trigger cell address.
n = Range("A1").Value

'Cell to act upon.
If Target.Address = "$A$1" Then
n = n + 1
Range("A2").Value = n

'What to do to the new cell.
'Move cursor.
Selection.Offset(rowOffset:=1, ColumnOffset:=0).Select
'Add blank row.
Selection.EntireRow.Insert
End If

End Sub
 
Upvote 0
Derek,
Thank you for responding. I have tried to follow your example, but...I'm getting a Compile Error: Expected Function or variable MsgBox when I change cell G4. Here is my code (Private line in yellow):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run DoM
End Sub
Partial DoM Macro:
Private Sub DoM()
'
' DoM Macro
' Macro recorded 3/21/2001 by Bob Lentz
'

'
Dim DayOfMonth As String
Range("G4").Select
DayOf = Format(Day(ActiveCell.Value), "dd") + 1
' MsgBox (DayOf)
If (DayOf > 31) Then DayOf = 33 - DayOf
' MsgBox (DayOf)
If (DayOf < 10) Then DayOfMonth = "0" & DayOf Else DayOfMonth = DayOf
' MsgBox (DayOfMonth)
Range("E34").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/SUM('01:" & DayOfMonth & "'!R[-30]C)"
Range("E35").Select
ActiveCell.FormulaR1C1 = "=SUM('01:" & DayOfMonth & "'!R[-30]C)"
...
As you may assume I am new to macros...

On 2002-04-04 06:30, Derek wrote:
Hi blentz2

Right click your sheet tab, left click View Code and paste this code in the white area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run "my macro name"
End Sub

change "my macro name" to the actual name of the macro you want to run

Hope this helps
regards
Derek
 
Upvote 0
Hi Bob
The event macro I gave you refers to the name of your normal module macro (it is not part of the event macro). You need to refer to the name of that macro as it appears at the top of its code and enclose it in double quotes like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run "DoM()"
End Sub

This event macro is then just a trigger that sets off your module macro "DoM()" when you make a change in G4. This is a more flexible way to do it since you can then also run "DoM()" independantly via a button or whatever.

If you want the whole lot as an event macro word it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then
Dim DayOfMonth As String
Range("G4").Select
DayOf = Format(Day(ActiveCell.Value), "dd") + 1
' MsgBox (DayOf)
If (DayOf > 31) Then DayOf = 33 - DayOf
' MsgBox (DayOf)
If (DayOf < 10) Then DayOfMonth = "0" & DayOf Else DayOfMonth = DayOf
' MsgBox (DayOfMonth)
Range("E34").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/SUM('01:" & DayOfMonth & "'!R[-30]C)"
Range("E35").Select
ActiveCell.FormulaR1C1 = "=SUM('01:" & DayOfMonth & "'!R[-30]C)"
...
End If
End Sub


Note that because the first line ie - "If Target.Address = "G$4" Then" - goes to a new line after "Then" you will need to add the line "End If" after your "DoM()" code, as a statement that it has reached the end of the action which was dependant upon the if condition.

Hope this makes sense
regards
Derek
 
Upvote 0
On 2002-04-05 05:24, Derek wrote:
Hi Bob
The event macro I gave you refers to the name of your normal module macro (it is not part of the event macro). You need to refer to the name of that macro as it appears at the top of its code and enclose it in double quotes like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run "DoM()"
End Sub

This event macro is then just a trigger that sets off your module macro "DoM()" when you make a change in G4. This is a more flexible way to do it since you can then also run "DoM()" independantly via a button or whatever.

If you want the whole lot as an event macro word it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then
Dim DayOfMonth As String
Range("G4").Select
DayOf = Format(Day(ActiveCell.Value), "dd") + 1
' MsgBox (DayOf)
If (DayOf > 31) Then DayOf = 33 - DayOf
' MsgBox (DayOf)
If (DayOf < 10) Then DayOfMonth = "0" & DayOf Else DayOfMonth = DayOf
' MsgBox (DayOfMonth)
Range("E34").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/SUM('01:" & DayOfMonth & "'!R[-30]C)"
Range("E35").Select
ActiveCell.FormulaR1C1 = "=SUM('01:" & DayOfMonth & "'!R[-30]C)"
...
End If
End Sub


Note that because the first line ie - "If Target.Address = "G$4" Then" - goes to a new line after "Then" you will need to add the line "End If" after your "DoM()" code, as a statement that it has reached the end of the action which was dependant upon the if condition.

Hope this makes sense
regards
Derek
Derek,
I have tried it both ways you suggested. I can not get the .change event to execute. G4 is a date field, if that matters. I want to change the date, let's say from 2/11/02 to 2/16/02. Then take the 16 and place it into formulas. The only way I can get it to work is to change date (G4) and run DoM manually. I have uncommented MsgBox statements within event version and get nothing...What silly thing am I missing????
TIA
 
Upvote 0
Bob

I think the answer is in the event macro.
The target address should read "$G$4" not "G$4" . Sorry, I didn't notice the typo.

regards
Derek
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top