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

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
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?

Code:
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Use InputBox Method instead like

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

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
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.

Code:
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:
Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try this way

Code:
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

Board Regular
Joined
Oct 30, 2002
Messages
112

ADVERTISEMENT

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:

Code:
Sepcifiedrange.Name = "Table"

What am I doing wrong?
Banjoe.
 

banjoe

Board Regular
Joined
Oct 30, 2002
Messages
112
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,535
Messages
5,548,624
Members
410,858
Latest member
RamIndia
Top