dynamic named range

[Gadeyne Dries]

Dear all,

Hope you can help me with the following.

I need to create a range (Dynamic range).
As all of you are aware: Insert-Name-Define...
However, the name of my range needs to be dependent on the entry of a
certain cell.

For instance: If I type in B1 "Mercedes", then I want the name of my named
range to be "Mercedes".
If later on I need to change this for any reason to "BMW", then I need the
name of my named range to change as well to "BMW"..

Hope you guys can help me out with this.

I am using Excel 2003

 

[Don Guillett]

Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address Then Exit Sub
On Error GoTo newone
With Range("a2:b22")
.Name.Delete
newone:
.Name = Target
End With
End Sub

 

[ozgrid.com]

Hello,

Name your Dynamic Named Range "MyRange" and use this code by right clicking
on your Sheet Name tab and choose "View Code" in here paste;


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
On Error GoTo InvalidName
'http://www.ozgrid.com/Excel/DynamicRanges.htm
Range("MyRange").Name = Target(1, 1)
End If

Exit Sub
InvalidName: MsgBox Target(1, 1) & " is not a valid name", vbCritical
End Sub

 

[ozgrid.com]

Oops, that does work Name the dynamic named range any name and duplicate
that name in B1, then right click on the sheet name tab and choose "View
Code" and in here paste;

Public strName As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
On Error GoTo InvalidName
'http://www.ozgrid.com/Excel/DynamicRanges.htm
Range(strName).Name = Target(1, 1)
End If

Exit Sub
InvalidName: MsgBox Target(1, 1) & " is not a valid name", vbCritical
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
strName = Target(1, 1)
End If
End Sub