Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand.

In my case based on the value in (say) cell D5 set the name box of E5 to the contents of D5.

I haven't seen a reference that would let me do this simply, so I suspect it's something that might have to be set through VBA.

Has anyone done this or can advise how to do this?


showing name box

share|improve this question
Are you talking about naming the range? I'm not clear on what you mean by "the name box of E5". – jonsca Aug 17 '11 at 14:53
I probably am talking about naming the range, though here the range may only be 1 cell. – Stephen Aug 17 '11 at 15:08

I believe this is what you're looking for

Range("A2:A6").Name = "FruitList"
share|improve this answer
snap, great minds thinking alike – Col Aug 17 '11 at 15:13

From VBA you can do it with

share|improve this answer
up vote 2 down vote accepted

Ok so I think what I'm looking at (with credit to @Col and @StevenV for the big point in the right direction) is = activecell.offset( 0, -1 ).value
share|improve this answer
That looks like it will name the single active cell with the name as whatever is in the cell immediately above. Is that what your trying to achieve? – Col Aug 17 '11 at 16:05
A bit shorter ActiveCell.Name = ActiveCell.Offset(0, -1).Value. – Kaze Aug 17 '11 at 16:24
@Col The cell to the left actually. – Stephen Aug 17 '11 at 17:45
@Stephen Doh. I wouldn't care if I hadn't actually been thinking row, column and still I got it wrong. – Col Aug 17 '11 at 17:47
yea, that's going to end up with "Apples" "Bananas" or the like as the range name, not "FruitList" That is what you asked for ('set the name box of E5 to the contents of D5') but I see how we looked at the image instead of that sentence. Oops :-) – StevenV Aug 17 '11 at 19:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .