Want to run macro only when specific cell changes

[blentz2]

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 ]

[lenze]

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

[Derek]

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

[Joe Was]

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

[blentz2]

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

[Derek]

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

[blentz2]

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

[Derek]

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