If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dynamic named range
Dear all,
Hope you can help me with the following. I need to create a range (Dynamic range). As all of you are awa 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 |
#2
|
|||
|
|||
dynamic named range
Right click sheet tabview codeinsert 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Gadeyne Dries" Gadeyne wrote in message ... Dear all, Hope you can help me with the following. I need to create a range (Dynamic range). As all of you are awa 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 |
#3
|
|||
|
|||
dynamic named range
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 -- Regards Dave Hawley www.ozgrid.com "Gadeyne Dries" Gadeyne wrote in message ... Dear all, Hope you can help me with the following. I need to create a range (Dynamic range). As all of you are awa 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 |
#4
|
|||
|
|||
dynamic named range
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 -- Regards Dave Hawley www.ozgrid.com "ozgrid.com" wrote in message ... 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 -- Regards Dave Hawley www.ozgrid.com "Gadeyne Dries" Gadeyne wrote in message ... Dear all, Hope you can help me with the following. I need to create a range (Dynamic range). As all of you are awa 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 |
Thread Tools | |
Display Modes | |
|
|