Excel range choose button in VBA form

Hello,
I would like to know if there is a button which can be included in a
userform in Excel 2002 VBA to let people choose a range of cells.

The kind of button (black&white plus a red arrow) which is used in the
graphics part to chosse the data columns of a new graph.

I think I have already seen something similar but can't find it again.

Thanks for your answers
Best regards
Phil

You can use the inputbox function with option 8 like this:

Dim rngSelection As Range
Set rngSelection = Application.InputBox( _
Prompt:="Please select a range.", _
Type:=8)

this puts a range as the
HTH

Philip

"Philipp" wrote:

> Hello,
> I would like to know if there is a button which can be included in a
> userform in Excel 2002 VBA to let people choose a range of cells.
>
> The kind of button (black&white plus a red arrow) which is used in the
> graphics part to chosse the data columns of a new graph.
>
> I think I have already seen something similar but can't find it again.
>
> Thanks for your answers
> Best regards
> Phil
>

You can use the inputbox function with option 8 like this:

Dim rngSelection As Range
Set rngSelection = Application.InputBox( _
Prompt:="Please select a range.", _
Type:=8)

this puts a range as the
HTH

Philip


"Philipp" wrote:

> Hello,
> I would like to know if there is a button which can be included in a
> userform in Excel 2002 VBA to let people choose a range of cells.
>
> The kind of button (black&white plus a red arrow) which is used in the
> graphics part to chosse the data columns of a new graph.
>
> I think I have already seen something similar but can't find it again.
>
> Thanks for your answers
> Best regards
> Phil
>

you can insert a refedit control from the toolbox.

HOWEVER refedits are very tricky controls.

It will crash excel when used on a modeless form.

You have to be carefull when the refedit is in a "container" on the
form. e.g. a tab, multipage or frame.. events may not be triggered at
the moment you expect it... it will get out of whack if you click a
control outside the container when the refedit has focus.

So for VERY simple modal forms: use a refedit.

ELSE include a button to show the Application.InputBox(Type:=8)
dialogbox to obtain the range.


--






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Philipp wrote :

> Hello,
> I would like to know if there is a button which can be included in a
> userform in Excel 2002 VBA to let people choose a range of cells.
>
> The kind of button (black&white plus a red arrow) which is used in
> the graphics part to chosse the data columns of a new graph.
>
> I think I have already seen something similar but can't find it again.
>
> Thanks for your answers
> Best regards
> Phil

Hi Phil

Use the RefEdit button

Peter Bircher

"Philipp" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I would like to know if there is a button which can be included in a
> userform in Excel 2002 VBA to let people choose a range of cells.
>
> The kind of button (black&white plus a red arrow) which is used in the
> graphics part to chosse the data columns of a new graph.
>
> I think I have already seen something similar but can't find it again.
>
> Thanks for your answers
> Best regards
> Phil

Philipp wrote:
> Hello,
> I would like to know if there is a button which can be included in a
> userform in Excel 2002 VBA to let people choose a range of cells.
>
> The kind of button (black&white plus a red arrow) which is used in the
> graphics part to chosse the data columns of a new graph.
>
> I think I have already seen something similar but can't find it again.
>
> Thanks for your answers
> Best regards
> Phil

Thank you for your answers. It works perfect.
Best regards Phil