Named Range names linked to cell

[Paul Sansom]

Hi
Is it possible to link the name of a named workbook range to a cell on a sheet.
I would like to make the name of the range dynamic?

When the title of my named ranges change i currently have to rename all the Named Range Names as well.

All ideas appreciated
Cheers Paul

 

[offthelip]

I think I understand what you want to do, This code will change then name of a named range where the name is given by the text in cell A1
You will need to define the named range first and then write the Name of it in cell A1. If the name is the same it doesn't change it.

Public previousname As String

Private Sub Worksheet_Change(ByVal Target As Range)
   
If Target.Address = "$A$1" Then
    If Cells(1, 1) <> previousname Then
    ActiveWorkbook.Names.Add Name:=Cells(1, 1), RefersTo:=ActiveWorkbook.Names(previousname)
    ActiveWorkbook.Names(previousname).Delete
    End If
   End If


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Address = "$A$1" Then
     previousname = Cells(1, 1)
    End If
End Sub

 

[Paul Sansom]

Yes thanks, i get this.
Compare the name value and then reset previousname to current value if the cell is selected before any change.
Sweet....... Many thanks