PDA

View Full Version : Accessing Range Name Causes Run-Time Error



L.H.T
11-03-2011, 12:57 PM
'lo all. First time posting here, so excuse any ignorance in regards to the community.

I'm attempting the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "There was a change to a cell in file " & Target.Name
End Sub
This raises "Run-time error '1004': Application-defined or object-defined error" whenever this event is raised in the sheet the code is in. Looking up 'Target.Name' in the Locals Window shows "<Application-defined or object-defined error>". Several other values in 'Target' show "<Unable to get <<property-name>> property from the Range class>", but when I change the code above to '
'Target.Address', it shows the proper information, so I'm a bit perplexed. I'm inclined to believe it has something to do with the fact it is a range for an event, though I honestly don't know. My Google-fu is failing me in this, so I'd be greatly appreciative if anyone can direct me how to get the name of the range when a cell is changed.

Thanks in advance.

P.S. I seem to be having troubles with indention with the VBA tags, if anyone can give me guidance in that for future use. Thanks.

mikerickson
11-03-2011, 01:04 PM
If Selection is a named range (the entire named range)
MsgBox Selection.Name.Name will return that name.

L.H.T
11-03-2011, 01:32 PM
Negative, mikerickson. 'Selection' is the cell that received focus after a change to the changed cell is made. Hitting the "Enter" key will have 'Selection.Address' be a reference to the cell below the cell changed, but you can't rely on this as you risk the user clicking on a cell to change focus instead of hitting the "Enter" key. I've even verified this by changing 'Target.Name' to 'Application.Selection.Address'. Thanks for the effort, though.

mikerickson
11-03-2011, 04:13 PM
I know what Selection means and how it acts in relation to the Worksheet_Change event.

What I was trying to show is the syntax for "given a Range Object, what Name (if any) refers to that range?"

The code in the OP will error if Target is not a named range.

"how to get the name of the range when a cell is changed"

As you pointed out, the .Address property will give you the address of the changed cell. This code will diferentiate between a named range changing and an unnamed range.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameOfTarget As String
NameOfTarget = vbNullString
On Error Resume Next
NameOfTarget = Target.Name.Name

On Error GoTo 0
If NameOfTarget = vbNullString Then
MsgBox Target.Address & " has changed. This is not a named range."
Else
MsgBox "The cell(s) of " & NameOfTarget & " have changed."
End If
End Sub
Note that this will return the Name only if all the cells in that named range have changed. i.e. if MyNamedRange refers to B1:B10 and cell B3 is changed, it will inform you that B3 is not a named range.

If you want to test if Target is in a particular named range, there are a variety of ways to do that. Which to use would depend on several questions:

(With MyNamedRange refering to B1:B10 as above)

What if Target is A2:J2, i.e. there is some overlap between Target and the named Range but not complete.

What if Target is A1:C20, i.e. the named range is a proper sub set of Target.

What if Target is a proper sub set of the named Range.

If there are multiple Named ranges involved, you have to consider situations where Target might be in multiple named ranges.

A few more specifics about your situation will help get a complete answer.

Paul_Hossler
11-03-2011, 07:48 PM
LHT --

That will only work if the entire Target range also happens to be a named range

Not every Range is .Name-d

Something like ...



Activesheet.range("A1:B2").Name = "Fred"
Activesheet.range("C1:Z26").Name = "Mary"
Activesheet.range("A:B").Name = "Bill"

Activesheet.range("A1:B2").value = 1234


will work since A1:B2 has a Name ("Fred")


Something like


Activesheet.range("A1:A1").value = 4321


Will not since there is no .Name assigned to just the Range A1

Paul

L.H.T
11-04-2011, 05:09 AM
That sounds like it'd be it, then, if I understand you properly, Paul_Hossler. I'm assuming the only way to find the name then is to check for intersection among all the named ranges of the sheet?

In regards to multiple cells, mikerickson, I hadn't quite gotten that far with this bit, but I'll likely just cycle through the cells in the changed range.

As for what I'm doing, I'm generating "overview sheets" of different sheets throughout multiple workbooks. The sheets are already generated with all the data in the sheet drawn from the same-named worksheet in the other workbooks. The data range from each workbook is named with a base64 string of the workbook's name (to get rid of the special characters) minus the "==" at the end. What I'm trying to do here is set it up to provide the ability to make changes in the "overview sheets" (whose workbook is open in the background, else nothing is changed) that propogates to the open workbook, which is why I need the name of the range the cell belongs to.

Also, thanks much to the both of you.

[Edit] I'll likely just throw in a "Save" button, so I can throw each named range over as appropriate instead of checking for events. Thanks for the help, again.

mikerickson
11-04-2011, 12:00 PM
If you have mulitple named ranges and want to run a routine when any cell in any of those named ranges is changed, something like this could be used. Looping is not needed. (A generalized code to find out which named range has been changed would involve a loop. If your named ranges are only one cell big or if they have some regularity about their size, that loop could also be avoided.)

If Not Application.Intersect(Target, Range("name1", "name2", "name3")) Is Nothing Then
Rem routine
End If