VBA code to select a range using a mouse by hilighting range

[banjoe]

I use the following snippet of code to prompt the user to type in a range from a message box which then becomes a named range in my spreadsheet. How can I alter the code to have the user select the range with his mouse instead of having to type it in the message box?

SpecifiedRange = InputBox("Specifiy the range to be named: 'Table'")
Range(SpecifiedRange).Select
MsgBox "This is the range you selected"
SpecifiedRange = Selection.Address
ActiveWorkbook.Names.Add Name:="Table", _
RefersTo:="=" & SpecifiedRange

Any help would be much appreciated. Banjoe.

 

[jindon]

Use InputBox Method instead like

Application.InputBox("select range",type:=8)

 

[banjoe]

Jindon:
Please bear with me a little - I'm still very new to VBA. I altered my original code (see below), and although the new code now allows me to select a range, that range does not become a named range as I intended. Whatever the active cell happens to be is what becomes named.

Specifiedrange = Application.InputBox("Specifiy the range to be named: 'Table'", Type:=8)
Range(Specifiedrange).Select
MsgBox "This is the range you selected"
Specifiedrange = Selection.Address
ActiveWorkbook.Names.Add Name:="Table", _
RefersTo:="=" & Specifiedrange

I need some more help to execute your suggestion. Also, I need some help to get the following lines of my code to work:

Range(Specifiedrange).Select
MsgBox "This is the range you selected"

The 2 above lines basically are supposed to confirm the range selected by the user.

Thanks for your help so far, I appreciate your patience with me. Banjoe.

 

[jindon]

try this way

Dim Specifiedrange As Range
Set Specifiedrange = Application.InputBox("Specifiy the range to be named: 'Table'", Type:=8)
MsgBox "This is the range you selected " & Specifiedrange.Address
Sepcifiedrange.Name = "Table"

 

[banjoe]

Jindon:
I used the code you posted but I am appearently doing something wrong because I get the following error:

Run-time error '424'
Object required

It "errors out" at the follow line:

Sepcifiedrange.Name = "Table"

What am I doing wrong?
Banjoe.

 

[jindon]

Sorry typo

Specifiedrange.Name = "Table"

 

[banjoe]

Jindon:
Success! It worked great. I should have caught that typo myself. I really, really appreciate your help with my question. Thanks soooo much. Banjoe.