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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
The Worksheet_Calculate event doesn't have a Target argument.

What is the formula in A1?
 
Upvote 0
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).
 
Upvote 0
When you get the error click the Debug button. Which line of code is highlighted?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top