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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Lee" wrote in message ...
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.

Just to elaborate, this allows the user to select using the mouse or by
typing in the range in the inputbox.

--
Regards,
Tom Ogilvy

"Ron de Bruin" wrote in 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)
www.rondebruin.nl



"Lee" wrote in message
...
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.

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)
www.rondebruin.nl



"Lee" wrote in
message ...
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.


.

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.


--
Regards,
Tom Ogilvy


"Lee" wrote in message
...
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)
www.rondebruin.nl



"Lee" wrote in
message ...
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.


.