Hi,

I have a event on a worksheet that is triggered when cell $A$5 is
changed. The problem is when cells are inserted or deleted from the
sheet then I need to update my macro.

Is there a way to target a named range?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
MsgBox "Hello"
End If
End Sub

Works

but

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "range name" Then
MsgBox "Hello"
End If
End Sub

Does not.

Any assistance will be appreciated.

Regards.

Sean

Hi Sean,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Test")) Is Nothing Then
MsgBox "Hello"
End If
End Sub

Replace Test withyour range name.


---
Regards,
Norman



"Sean" wrote in message
oups.com...
Hi,

I have a event on a worksheet that is triggered when cell $A$5 is
changed. The problem is when cells are inserted or deleted from the
sheet then I need to update my macro.

Is there a way to target a named range?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
MsgBox "Hello"
End If
End Sub

Works

but

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "range name" Then
MsgBox "Hello"
End If
End Sub

Does not.

Any assistance will be appreciated.

Regards.

Sean

Hi,
you may be able to overcome something like insertion of rows by naming the
range, but i am not sure if this addresses your question.
--
David


"Sean" wrote:

Hi,

I have a event on a worksheet that is triggered when cell $A$5 is
changed. The problem is when cells are inserted or deleted from the
sheet then I need to update my macro.

Is there a way to target a named range?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
MsgBox "Hello"
End If
End Sub

Works

but

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "range name" Then
MsgBox "Hello"
End If
End Sub

Does not.

Any assistance will be appreciated.

Regards.

Sean

In the new version of Visual Studio 2005
There are tools for developing office applications.
You can write code that will target the on change event of a name
range.

There is only a beta version available of VSTO
And you have to write it in VB.net or C#.net.

If you want to know more about this option let me kno

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=39119

Thanks Norman, Works a treat, even made sense after I thought about it.

Sean