How to: User selected range?
 

My goal is to have a spreadsheet with a few thousand rows of data,
including a coloum of unique numbers. User should be able to select by
dragging a range, ad get in return, a comma deliminated list of all of
the numbers in that range. I have the second part ready. A function
which you give a range, and it returns the list.

Now, how do I get that range from the user? Ideally, the formula using
that function would use a range name, so my problem becomes how to get a
user to select a range and hit a macro button, or hit a macro button and
select a range, with the selected range being assigned that particular
name...

How to: User selected range?
 

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
r.Select
MsgBox (r.Address)
End Sub

This will work whether the user types the range in the box or uses the mouse.
--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

My goal is to have a spreadsheet with a few thousand rows of data,
including a coloum of unique numbers. User should be able to select by
dragging a range, ad get in return, a comma deliminated list of all of
the numbers in that range. I have the second part ready. A function
which you give a range, and it returns the list.

Now, how do I get that range from the user? Ideally, the formula using
that function would use a range name, so my problem becomes how to get a
user to select a range and hit a macro button, or hit a macro button and
select a range, with the selected range being assigned that particular
name...

How to: User selected range?
 

Great, but how do I assign that range to MyRangeName?




Gary''s Student wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
r.Select
MsgBox (r.Address)
End Sub

This will work whether the user types the range in the box or uses the mouse.

How to: User selected range?
 

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub

--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

Great, but how do I assign that range to MyRangeName?




Gary''s Student wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
r.Select
MsgBox (r.Address)
End Sub

This will work whether the user types the range in the box or uses the mouse.

How to: User selected range?
 

Nope. It runs with no errors, but the cells covered by that range do
not change to the selected cells. The range remains the same...


Gary''s Student wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub

How to: User selected range?
 

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
MyRangeName.Select
End Sub

--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

Nope. It runs with no errors, but the cells covered by that range do
not change to the selected cells. The range remains the same...


Gary''s Student wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub

How to: User selected range?
 

Still no joy. Runs without errors, but MyRangeName is not being updated
to reflect the choice...






Gary''s Student wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
MyRangeName.Select
End Sub

How to: User selected range?
 

Never mind, I got it:

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
ThisWorkbook.Names.Add Name:="MyNameRange", _
RefersTo:=r, Visible:=True
End Sub






Phil Smith wrote:
Still no joy. Runs without errors, but MyRangeName is not being updated
to reflect the choice...






Gary''s Student wrote:

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
MyRangeName.Select
End Sub