Change in cell value not triggering Macro

haydnc

New Member
Joined
Apr 16, 2002
Messages
10
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi. Maybe.....
Check the address out.

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

Regards,
Colo
 
Upvote 0
Thanks for trying Colo.
Unfortunately, absoluting the full cell address has no effect.
Regards
Haydn
 
Upvote 0
Hello again, Pls try this.<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$7" Then Call CalcSumMacro
End Sub</pre>
This message was edited by Colo on 2002-04-17 19:43
 
Upvote 0
Thanks for your help Colo.
I've tried the amended code suggested but it's now coming up as a syntax error.
Regards
Haydn
 
Upvote 0
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 ??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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