MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Setting a Dynamic Range name via a textbox


Posted by Jason A on January 27, 2002 1:20 AM

I'm trying to set a dynamic range with a name after the name has been entered in a textbox. As the dynamic range may already exist (under a different name) do I need to delete the first one before I set the new one?
My code so far looks like this but doesn't work. Any help would be appreciated.

Dim Floorname As String
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Change named range with entered name

Floorname = Set_Floor_1.TextBox1.Text

Sheet1.Names.Add Name:=Floorname, RefersToR1C1:= _
"='Room Data'!R4C1:OFFSET('Room Data'!R4C1,COUNT('Room Data'!R4C1:R155C1)-1,0)"

End Sub

Cheers,

Jason


Posted by Aladin Akyurek on January 27, 2002 5:07 AM

> I'm trying to set a dynamic range with a name after the name has been entered in a textbox. As the dynamic range may already exist (under a different name) do I need to delete the first one before I set the new one?

I'd say No. The same cell or range of cells may have multiple names or, as it were, aliases.

===========