Using Named Range in Worksheet Change Event

[karabiner8]

Hi,

I'm developing spreadsheet where the user will enter a path to a network drive in cell B6 of a worksheet named Input. Excel automatically creates a hyperlink after the path is entered. I'd like to use a Worksheet Change event to remove the hyperlink as soon as it's created but instead of entering "$B$6" as the target address I'd like to use the Name I've assigned to the range ("ScrdPath"). This way I don't have to worry about the target cell moving while I'm changing the layout of the worksheet.

This code works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$6" Then
     Range("$B$6").Hyperlinks.Delete
End If
End Sub

This code does nothing (no errors reported):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ScrdPath") Then
     Range("ScrdPath").Hyperlinks.Delete
End If
End Sub

I've checked to make sure that I named cell B6 ScrdPath. Can anyone tell me what I'm doing wrong?

Thanks

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ScrdPath")) Is Nothing Then
     Target.Hyperlinks.Delete
End If
End Sub

 

[Richard Schollar]

You need to access the Address property of the named range:

If Target.Address = Range("ScrdPath").Address Then

 

[karabiner8]

Thank you VoG and Richard Schollar. I took a little bit from both your replies and got this to work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ScrdPath").Address Then
     Target.Hyperlinks.Delete
End If
End Sub

Thanks for saving me a ton of grieve.

Karabiner8

 

[Askjerry]

Thanks for that post!

I have been searching the internet 4 hours trying to figure that one out.

The code is working flawlessly for me now.

HUGE THANKS!

Jerry

 

[leterrier]

This has also helped me, thank you, this forum is wonderful!!!