MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Testing for the Named Range of Target

  Asked By: Jody    Date: Oct 21    Category: MS Office    Views: 5424

I am redoing the GUI of a workbook by starting with the GUI and
want the flexibility of moving things around during development for
the best intuitive interface. In conventional programming, I learned
the benefits of position independent code...In that vein...

I'm having difficulty using a Named Range (cell) and be error
tolerant when testing for that cell being a specific Target.

Short version:
I want to test if a Target is a specific Range Name.
Perhaps I'm barking up the wrong bark. (:-)

I have a cell (lets say R3) with a number in it (Excel makes it a
Type "Double") that the user can change.

I can easily determine when its value is changed in the sheet's
Change() sub and correct, for example, a "too big" error like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$R$4" Then ' <--- Herein lies the rub.
If Target.Value > 399 Then Target.Value = 399
End If

End Sub
No Prob... The proverbial charm at work.

HOWEVER, this is position DEpendent (spreadsheet position). If I
decide to move that cell for a better GUI, I must change the code.

I would think, I can simply name the cell, lets say to "MyData", and
then test Target for that Range Name.
I can't seem to determine if it is the desired RangeName in an "IF"
test like the Row/Col (address) test above !

All cells have an address, but not a Range Name.

The main problem seems to be that the test errrs if I change a cell
which has *NO* range name.

I may be having ancestral problems here (:-)

"Target.Name.Name" returns the Range name ok, but only when changing
any Named Range. Changing a non-named cell gives an "Application or
Object defined error, '1004'.

So I need something to be tolerant of a non-Named Range....
If Target.Name.Name <> null then ' If it's any NamedRange

If Target.Name.Name = "MyData" then ' If it's the one I want.
If Target.Value > 399 Then Target.Value = 399 ' Fix it

Else ' If not a NamedRange

I have tried so much my brain hurts too mush: <-- interenting
slip of the fingers on the word "much", eh?



6 Answers Found

Answer #1    Answered By: Dominic Murphy     Answered 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".

Answer #2    Answered By: Jeffrey Washington     Answered On: Oct 21

If you are using .address to compare, then try something like this:

if target.address = [myData].address then 'bingo!

Answer #3    Answered By: Landra Schmidt     Answered On: Oct 21

While the functional use of VBA's Error-Trap is interesting, I have
many other tests in my Change() Sub. Errors in any of the other
sections of the procedure would have to be handled with the same
"do-nothing" routine; which may not be appropriate.
While it works, the "Not Intersect is Nothing" "double negative"
test seemes awkward. I selected Tou's because the address  technique
seems more intuitive. Actually, looking at it, it appears to be "the
correct way". I tried, but just couldn't get the correct  object

However, I did test  both techniques and show the actual tests here
for completeness should anyone else benefit.
Regards, Steve, Noskowicz.
Because the code  also does corrections on cell  values, I turn off
events first in the Change() Sub [Application.EnableEvents = False]
and back on at the end [Application.EnableEvents = True].

' The original "fixed-cell" [direct address reference] code.

If Target.Address = "$X$3" Then
' Handle the change  in cell X3
ElseIf Target.Address = "$X$4" Then
' Handle the change in cell X4
End If

The following techniques, using Cell Range-Names, allow relocating the
cells on the sheet without re-writing the code. Both techniques do
not error  if a non-Named cell is changed.

Note that initially, cell X3 is RangeNamed Mem_First and X4 is
RangeNamed Mem_Last

"Address" technique:
If Target.Address = Sheet3.Range("Mem_First").Address Then
' Handle the change in cell X3
ElseIf Target.Address = Sheet3.Range("Mem_Last").Address Then
' Handle the change in cell X4
End If

"Intersect" technique:
If Not Intersect(Target, Range("Mem_First")) Is Nothing Then
' Handle the change in cell X3
ElseIf Not Intersect(Target, Range("Mem_Last")) Is Nothing Then
' Handle the change in cell X4
End If

Answer #4    Answered By: Alexander Bouchard     Answered On: Oct 21

No, not necessary. What you do is put

On Error Resume Next

immediately before the statement in question and then

On Error GoTo 0

immediately after it.

From my example:

> XX = "No"
> On Error Resume Next
> XX = Target.Name.Name
> On Error GoTo 0

Better, though, is to encapsulate the bit of code  involved into a
function (or subroutine). This is a little function from my common
functions that returns the name of a range  if it has one.

Public Function CellRangeName(ByVal TheCell As Range) As String
CellRangeName = ""
On Error Resume Next
CellRangeName = TheCell.Name.Name
End Function

In this case, the On Error will be cancelled when the function finishes.

I agree with the awkward look of the double  negative, but there is no "is
something" unfortunately.

I also like Tou's comparison of addresses. It certainly has a "clean" feel
to it.

Answer #5    Answered By: Erika Evans     Answered On: Oct 21

I do this all the time... But I use a variable to catch the error number  so
I can immediately reset error  trapping. Otherwise we get into resetting
error trapping all over the place.

On Error Resume Next
... Line of code  that could error
lgErrNum = Err.Number
On Error GoTo 0
Select case lgErrNum
Case ....
Case ....
Case Else
End select

Answer #6    Answered By: Jermaine Powell     Answered On: Oct 21

I figured I needed to do more reading about VBA's On Error. The
positioning of those statements in your code  and the Goto 0 alerted me
that something more was to be learned. I briefly pondered asking, but
I had my immediate solution.

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.