Select range then name it

[Guest]

Hi,

Looking for code that will select/highlight a range and then name it. The
range is an Excel download that varies in size. A user can run several
downloads per day hence the need to automate the process. It would be cool to
have some kind of pop up box that allows the user to type in a name each time.

Thanks.

 

[ward376]

Try:
sub definename()
activecell.currentregion.select
Application.Dialogs(xlDialogDefineName).Show
end sub
This will select the current region of the active cell and open the
define names dialog; the user can just type the name in and hit enter
to name the range.

Caveats:
1. This aassumes that the active cell is in the downloaded range.
2. You'll have to develop this further if you have multiple downloaded
ranges in contiguous ranges on the same sheet. This will work as long
as each download is separated from others.

Note: Depending on how the info is downloaded, the range may already
be named. (i.e. MS Query) Check your name drop-down. This would make
it a lot easier to work with multiple contiguous downloaded ranges.

Cliff Edwards

 

[ward376]

Or if you can get the user to select the downloaded range you can use
something like this:

Dim sRange As Range
Dim newName As String

Sub defineRange()
Set sRange = Application.InputBox("Select the range.", "Name
Downloaded Range.", Selection.Address, , , , , 8)
newName = Application.InputBox("Enter the new name.")
ActiveWorkbook.Names.Add name:=newName, RefersToR1C1:=sRange
End Sub

Cliff Edwards

 

[ward376]

code didn't wrap right -

Set sRange = Application.InputBox("Select the range.", "Name
Downloaded Range.", Selection.Address, , , , , 8)

is one line

 

[Guest]

Hi Cliff.

Thanks for your reply(s). I think I am almost there. I prefer
ActiveCell.CurrentRegion.Select as it sets the range automatically. However I
must have something missing as it does everything but name the range. Can you
see what I am missing?

Dim newName As String

Sub defineRange()
ActiveCell.CurrentRegion.Select
newName = Application.InputBox("Enter the new name.")
End Sub

 

[Dave Peterson]

Sub defineRange()
Dim newName As String
ActiveCell.CurrentRegion.Select
newName = Application.InputBox("Enter the new name.")
if newname = "" then
exit sub
end if
activecell.currentregion.name = newname

End Sub

 

[ward376]

Option Explicit
Dim sRange As String
Dim newName As Variant

Sub defineRange()
sRange = ActiveCell.CurrentRegion.Address
newName = Application.InputBox("Enter the new name.")
If newName = "" Then
Exit Sub
ElseIf newName = False Then
Exit Sub
ElseIf IsNumeric(newName) Then
Exit Sub
End If
ActiveWorkbook.Names.Add Name:=newName, RefersToR1C1:=sRange
End Sub

Notes: That was a bad example, me declaring an inputbox as a string;
it should always be a variant. It can be a string, boolean or numeric.
I may still be overlookinga data type there.

Cliff Edwards

 

[Dave Peterson]

If I'm expecting text, I'll declare the variable that will hold the value of an
Inputbox as a string.

And I'd use inputbox, not application.inputbox.