Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Change in cell value not triggering Macro

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

    Default

    Hi,
    If anyone can assist with this query, I'd be extremely grateful. I have a 2-worksheet Excel 2000 workbook. I have a macro, "CalcSumMacro", that works fine when assigned to a button but I want the macro to run when I change the value of cell L7 (on sheet 2).
    Under sheet 2, my code is shown below:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "L$7" Then Application.Run "CalcSumMacro()"
    End Sub

    I am not getting any compile errors. Can anyone advise why this isn't working please? Thanks in advance.
    Cheers
    Haydn

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi. Maybe.....
    Check the address out.

    >If Target.Address = "L$7"
    If Target.Address = "$L$7"

    Regards,
    Colo

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

    Default

    Thanks for trying Colo.
    Unfortunately, absoluting the full cell address has no effect.
    Regards
    Haydn

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hello again, Pls try this.


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$L$7" Then Call CalcSumMacro
    End Sub



    [ This Message was edited by: Colo on 2002-04-17 19:43 ]

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

    Default

    Thanks for your help Colo.
    I've tried the amended code suggested but it's now coming up as a syntax error.
    Regards
    Haydn

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 20:14, haydnc wrote:
    Thanks for your help Colo.
    I've tried the amended code suggested but it's now coming up as a syntax error.
    Regards
    Haydn
    Where is the error coming from ??
    Are the Sheets Events Enabled ??
    Kind Regards,
    Ivan F Moala From the City of Sails

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

    Default

    Hi Ivan,
    No, the sheets are not event enabled - either sheet works independently of the other and neither requires an event. Although the macro works fine from a button, just in case there is something in the macro code itself, here it is:
    Sub CalcSumMacro()
    '
    ' CalcSumMacro Macro
    '
    Application.ScreenUpdating = False
    Range("I7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pricing").Select
    Range("J9").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("J16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Savings").Select
    Range("I12").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("Pricing").Select
    Range("J12").Select
    Selection.Copy
    Sheets("Savings").Select
    Range("L12").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.ScreenUpdating = True
    End Sub
    The macro is nothing special and deleting the lines relating to Application.ScreenUpdating which removes screen flicker when running the macro has no effect either.
    Thanks for your help.
    Regards
    Haydn

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Haydn Cooper
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have now managed to get this working after relooking at Colo's suggestion and Ivan's prompt.
    Many thanks for your help. Kind regards
    Haydn

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
  •