automatically updating range names??

[AndyKoller]

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

 

[VoG]

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

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

then press ALT + Q to return to your sheet.

 

[VoG]

Actually this is better (deletes the old name first)

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

 

[AndyKoller]

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:

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....

 

[VoG]

The Worksheet_Calculate event doesn't have a Target argument.

What is the formula in A1?

 

[AndyKoller]

The formula in cell A1 is very simple

=D5

and its defined to be a text

 

[VoG]

Try this 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).

 

[AndyKoller]

I get a Run time error 1004.



Probably the way i copy it into the VBA.???

 

[VoG]

When you get the error click the Debug button. Which line of code is highlighted?

 

[AndyKoller]

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?