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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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,651
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,651
The Worksheet_Calculate event doesn't have a Target argument.

What is the formula in A1?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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,651
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?
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top