Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Jody Mills   on Oct 21 In MS Office Category.

  
Question Answered By: Dominic Murphy   on Oct 21


I seem to recall that it is not actually possible to ask a cell  whether it
has a name. There are a couple of ways to work around this.

1) Assign the name of the cell to a string, but: (a) prefill the string
with a special value or an empty string, and (b) put the assignment
statement under On Error Resume Next. E.g.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then
Dim XX As String
XX = "No"
On Error Resume Next
XX = Target.Name.Name
On Error GoTo 0
Range("A1").Value = XX
End If
End Sub

puts into A1 the name of the cell if it has one, or "No".

2) Use Intersect. E.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("aa")) Is Nothing Then
Range("A1").Value = "aa"
ElseIf Not Intersect(Target, Range("bb")) Is Nothing Then
Range("A1").Value = "bb"
ElseIf Target.Address <> "$A$1" Then
Range("A1").Value = "Nothing"
End If
End Sub

does specific  checks for names "aa" and "bb".

Share: 

 

This Question has 5 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Testing for the Named Range of Target Or get search suggestion and latest updates.


Tagged: