Is it Possible ? Auto Updating Names of Named Ranges

[RAF43]

Hello Everyone,

Is it possible to have the "name" of a named range change automatically to match the text of a specific cell as it changes ?

For example: Assume there is a named range for cell A1 that is Team_A. That also happens to match the text of cell A1. If the text in cell A1 happens to change to Team B, is it possible to have the named range also change automatically to Team_B and all referenced formulas change accordingly ?

The reason: This is probably not a best practices approach but it works. I'm using the drop down named range selection box to quickly find and locate the cursor to a given cell by simply selecting a named range in a protected worksheet. If the Named range matches the name of a specific cell, then that cell text changes, I'd like the name range to update and reflect the new text.

You guys are the best - Many thanks !
Randy

 

[Rick Rothstein]

I think this Change event code will do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim SavedRefersTo As String
  If Target.Address(0, 0) = "A1" Then
    SavedRefersTo = Range("A1").Name.RefersToR1C1
    With ActiveWorkbook.Names(Range("A1").Name.Name)
      .Name = Target.Value
      .RefersToR1C1 = SavedRefersTo
    End With
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[RAF43]

Hi Rick,

Thanks for the reply.

Sorry - I couldn't make the VBA code work. To recap: I have a range name that matches the text in cell A1. If the text in cell A1 is changed I want the range name to also change to match the cell A1 text. Possible ?

Thank you,
Randy

 

[Rick Rothstein]

Sorry - I couldn't make the VBA code work. To recap: I have a range name that matches the text in cell A1. If the text in cell A1 is changed I want the range name to also change to match the cell A1 text. Possible ?

Possible? Yes... that is exactly what my code does. What do you mean that you could not make the VBA code work? Just saying "it did not work" tells us nothing usefult... exactly what did you do and describe what happened when you did it.

 

[RAF43]

You're right - sorry. I'll explain better.

I copied the code and pasted it in my worksheet's VBA code box as instructed.
I changed the A1 reference in the code (3 places) to C5. This is the cell that contains the text in my actual worksheet that I want the named range to match.
I saved the worksheet as macro enabled.
Closed worksheet and reopened .... no change to the exiting named ranges, not do they match the text in C5.

Thanks for the assistance. I'm guessing the Target.Address (0, 0) also needs to be changed to reference something, but I'm a newbi to VBA coding.

Thanks for your patience,
Randy

 

[Rick Rothstein]

I saved the worksheet as macro enabled.
Closed worksheet and reopened .... no change to the exiting named ranges, not do they match the text in C5.

The code will not do anything UNTIL you make a change to cell C5 (hence the procedure name Worksheet_Change). Try changing C5 to something else and then change it back to the text currently in Cell C5... did that update everything correctly for you?

 

[RAF43]

Wow ... super cool. Yes it worked perfectly. THANK YOU.

A couple of refinements if possible: If the referenced cell is two words I have to enter it with a (underscore) "_" between the two words for it to update correctly. For example Company 1 needed to be entered as Company_1 for the range name to update properly. Any work around ?

Secondly: If I have several range names, I want them all to update as cells are changed, what part of the VBA code would I duplicate before the end statement.

Would the red text below I've inserted be correct ....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SavedRefersTo As String
If Target.Address(0, 0) = "A1" Then
SavedRefersTo = Range("A1").Name.RefersToR1C1
With ActiveWorkbook.Names(Range("A1").Name.Name)
.Name = Target.Value
.RefersToR1C1 = SavedRefersTo
If Target.Address(0, 0) = "C2" Then
SavedRefersTo = Range("C1").Name.RefersToR1C1
With ActiveWorkbook.Names(Range("C1").Name.Name)
.Name = Target.Value
.RefersToR1C1 = SavedRefersTo
End With
End If
End Sub

Thank you again - This Board is simply amazing.
Randy.

 

[RAF43]

Managed to modify to change selected range names, but not the underscore requirement if name is two words.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SavedRefersTo As String
If Target.Address(0, 0) = "C5" Then
SavedRefersTo = Range("C5").Name.RefersToR1C1
With ActiveWorkbook.Names(Range("C5").Name.Name)
.Name = Target.Value
.RefersToR1C1 = SavedRefersTo
End With
End If
If Target.Address(0, 0) = "C18" Then
SavedRefersTo = Range("C18").Name.RefersToR1C1
With ActiveWorkbook.Names(Range("C18").Name.Name)
.Name = Target.Value
.RefersToR1C1 = SavedRefersTo
End With
End If
End Sub

Thank you again. I sincerely appreciate your help !
Randy

 

[Rick Rothstein]

This should be all you need...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim SavedRefersTo As String
  If Target.Address(0, 0) = "C5" Then
    SavedRefersTo = Range("C5").Name.RefersToR1C1
    With ActiveWorkbook.Names(Range("C5").Name.Name)
      .Name = Replace(Target.Value, " ", "_")
      .RefersToR1C1 = SavedRefersTo
    End With
  End If
End Sub

 

[mikerickson]

What do you want to happen to the formulas that use that named range.

If you have "TeamA" in A1 and a named range TeamA that refers to A1 and a formula =OFFSET(TeamA, 1,0)

What do you want the formula to be after the user enters "TeamB" into A1?
Also, after the entry, do you want there to still be a named range called TeamA?