Select range then name it

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ant - 21 Nov 2007 03:47 GMT

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.

Reply to this Message

ward376 - 21 Nov 2007 06:28 GMT

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

Reply to this Message

ward376 - 21 Nov 2007 06:58 GMT

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

Reply to this Message

ward376 - 21 Nov 2007 07:03 GMT

code didn't wrap right -

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

is one line

Reply to this Message

Ant - 21 Nov 2007 22:52 GMT

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

> Or if you can get the user to select the downloaded range you can use
> something like this:
[quoted text clipped - 10 lines]
>
> Cliff Edwards

Reply to this Message

Dave Peterson - 21 Nov 2007 23:22 GMT

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

> Hi Cliff.
>
[quoted text clipped - 22 lines]
> >
> > Cliff Edwards

Signature

Dave Peterson

Reply to this Message

ward376 - 22 Nov 2007 05:51 GMT

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

Reply to this Message

Dave Peterson - 22 Nov 2007 12:57 GMT

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.

> Option Explicit
> Dim sRange As String
[quoted text clipped - 18 lines]
>
> Cliff Edwards

Signature

Dave Peterson

Reply to this Message