automatically updating range names??

AndyKoller

New Member
Joined
Dec 27, 2009
Messages
9
Hi everyone, i tried to find a solution for following situation:

I have a range of 9 cells located in A2 to A10. The name of this range should be whatever the text in cell A1 is.

The Problem is that the content of cell A1 can change since it is reflecting the content of another cell on a different sheet.

My probelm is that once i define the range to be named according A1 it will keep that name, even if the content of A1 changes.

How can i program in VBA that the name of the cells in range A2 to A10 always is whatever isthe text in A1 is? If Aq changes the name of the cells in range A2 to A10 should change as well; Plus the old name should be deleted.
I think it might work with some event trigger and then a automatic naming of a range. I tried a few things but nothing really worked. (not very exeprianced vit VBA)

Any help is welcome.

Andy
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Re: automatically updateing range names??

Hello and welcome to MrExcel.

Assuming that A1 contains a formula try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Calculate()
Range("A2:A10").Name = Range("A1").Value
End Sub

then press ALT + Q to return to your sheet.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Actually this is better (deletes the old name first)

Code:
Private Sub Worksheet_Calculate()
Range("A2:A10").Name.Delete
Range("A2:A10").Name = Range("A1").Value
End Sub
 

AndyKoller

New Member
Joined
Dec 27, 2009
Messages
9
Hi Peter,
after copying your code to the VBA of that sheet the range A2:A10 still did not have the name equal the content in cell A1

Usually if i high-light the range it should tell me its name in to top left field to the left of the formula, shouldn't it?

I also tried to add a trigger vfor it to re-calculate the range name but that does not work either:

Code:
Private Sub Worksheet_Calculate()
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "A1" Then Range("A2:A10").Name.Delete
Range("A2:A10").Name = Range("A1").Value
End Sub


I have a simplified version of my excel sheet but dont' know how to up-load it...

Thanks for your help. sorry for beeing so basic....
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

The Worksheet_Calculate event doesn't have a Target argument.

What is the formula in A1?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try this code

Code:
Private Sub Worksheet_Calculate()
Range("A2:A10").Name.Delete
Range("A2:A10").Name = Range("A1").Value
End Sub

Change D5 and the range name should change to the text in D5 (it does for me).
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
When you get the error click the Debug button. Which line of code is highlighted?
 

AndyKoller

New Member
Joined
Dec 27, 2009
Messages
9
ok, i don't know waht i did but it seems to be working now.

Can you explain me why it does not need a trigger event and always seems to up-date right away?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,875
Messages
5,574,762
Members
412,617
Latest member
mlharris
Top