Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Want to run macro only when specific cell changes

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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









  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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








  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •