Change in cell value not triggering Macro

[haydnc]

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

[Colo]

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

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

Regards,
Colo

[haydnc]

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

[Colo]

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 ]

[haydnc]

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

[Ivan F Moala]

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 ??

[haydnc]

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

[haydnc]

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