up vote 3 down vote favorite
share [g+] share [fb]

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?

edit

showing name box

link|improve this question
100% accept rate
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
feedback

3 Answers

up vote 4 down vote

I believe this is what you're looking for

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

From VBA you can do it with

activesheet.range("A2:A6").name="Fruitlist"
link|improve this answer
feedback
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.name = activecell.offset( 0, -1 ).value
link|improve this answer
1  
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
2  
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
1  
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
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.