vary named ranges by location and name

[mtm789]

I am attempting to name a range by using an inputbox and would also like to set a variable in the macro so the newly named range in associzted with the current location.

Thanks
Todd

 

[PBAPaul]

mtm789

Try this code:


Sub GetRangeName()
Dim Msg As String
Dim Ans As String
Dim SheetName As String

'Assuming range has been selected
Msg = "Please enter a name for the selected range" & vbLf
Msg = Msg & "Note: This name should not contain spaces; must start with a letter and not be longer than 255 characters."
Ans = InputBox(Msg, "RANGE NAME", "&quot
If Ans = "" Then
MsgBox "Name not created"
Exit Sub
End If
SheetName = ActiveSheet.Name
SheetName = "='" & SheetName & "'!" & Selection.Address
ActiveWorkbook.Names.Add Name:=Ans, RefersTo:=SheetName
End Sub

You might want to do a bit more coding to check whether the name is a valid name. See Excel's help topic "Guidelines for naming cells, formulas, and constants in Microsoft Excel" for more information

Paul

 

[mtm789]

Paul

Thank you very much! It was a huge help