Select column at run-time

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Is there a way to prompt a user to select a column and
that selection be passed to a sub-routine as a variable?
I think it would be fairly easy to do this where users
type in an input box, but I would like for them to
actually be able to select the column instead of typing.
 
Try this

Sub test()
Dim mycell As Range
Set mycell = Application.InputBox( _
prompt:="Select a range", Type:=8)

mycell.Copy Sheets("Sheet2").Range("A1")

End Sub
 
Just to elaborate, this allows the user to select using the mouse or by
typing in the range in the inputbox.
 
Thanks. This sort of works. It does place the cell
reference in the input box. However, what I'm trying to
do is sort the whole spreadhseet based on the cell
selected. It is passing the value of the cell along when
in fact I just need to pass the cell number as a
reference.
-----Original Message-----
Try this

Sub test()
Dim mycell As Range
Set mycell = Application.InputBox( _
prompt:="Select a range", Type:=8)

mycell.Copy Sheets("Sheet2").Range("A1")

End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Lee" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
It passes a reference to the cell. You can use that to do your sorting.

Dim mycell As Range
Set mycell = Application.InputBox( _
prompt:="Select a range", Type:=8)
Call Mysort( mycell)


Sub Mysort(rng as Range)
cell.Sort Key1:=rng, Order1:=xlAscending
End Sub


When you don't know the answer, it is nicer to ask people how their
suggestion could be used to do this or do that rather than tell them it
can't be used for what you want. And after all, your original question said
nothing about sorting, only about selecting a range with a mouse and passing
it to a subroutine.
 
Back
Top