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
------------------------------------------------------------------------
mowen's Profile: http://www.excelforum.com/member.php...fo&userid=8463
View this thread: http://www.excelforum.com/showthread...hreadid=466759

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"

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

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



mowen wrote:

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
------------------------------------------------------------------------
mowen's Profile: http://www.excelforum.com/member.php...fo&userid=8463
View this thread: http://www.excelforum.com/showthread...hreadid=466759

--

Dave Peterson

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


--
mowen
------------------------------------------------------------------------
mowen's Profile: http://www.excelforum.com/member.php...fo&userid=8463
View this thread: http://www.excelforum.com/showthread...hreadid=466759

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.

mowen wrote:

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

--
mowen
------------------------------------------------------------------------
mowen's Profile: http://www.excelforum.com/member.php...fo&userid=8463
View this thread: http://www.excelforum.com/showthread...hreadid=466759

--

Dave Peterson