Macro not running

M

mowen

Why wouldn't this macro work when I copy it to the module?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$j$2" Then
On Error Resume Next
Application.EnableEvents = False
Me.Name = Target.Value
Application.EnableEvents = True
End If
End Sub

Run
 
J

Jim Cone

mowen,

Because the Target.Address does not equal "$j$2".
Either change the lowercase j to upper case or add
"Option Compare Text" to the top of the module.

Jim Cone
San Francisco, USA


"mowen"
<[email protected]>
wrote in message
Why wouldn't this macro work when I copy it to the module?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$j$2" Then
On Error Resume Next
Application.EnableEvents = False
Me.Name = Target.Value
Application.EnableEvents = True
End If
End Sub
Rune
mowen
 
D

Dave Peterson

Or you could use a different way to check:

If Target.Address = "$J$2" Then

is equivalent to these two lines:

if target.cells.count > 1 then exit sub
if intersect(target, me.range("j2")) is nothing then exit sub
 
M

mowen

This code have work before and yes I have now change to upper case.
For me its looks like I have placed it wrong, because when I try to run
the macro the Macro windows pop up and want me to write the macro name
and make a new macro.
Rune
 
D

Dave Peterson

This kind of procedure doesn't get run by the user running the macro
(tools|macro|macros...).

It gets run by the user making a change in the worksheet.

So rightclick on the worksheet tab that should have this behavior. Select View
code and paste your corrected code into that code window.

(And clean up whereever the code was before.)

Then back to excel and change J2 to see what happens.
 
Top