Prompting User to Select a Data Range

[Saladsamurai]

When you use the 'Chart Wizard' it gives the user the option of clicking on a little 'button thing' and then selecting the data range that they want to the chart to reference. It looks like this:

http://i12.photobucket.com/albums/a220/saladsamurai/promptything.jpg

Is there any way to mimic this in VBA? For example, let's say that I have a variable 'MyRange' I want to prompt the User to select a Range and then hit OK; then the selected range will be stored in the variable 'MyRange'

I have a feeling that I will need to use a UserForm for this.

Any ideas on how to mimic the prompt button or at least its functionality?

Thanks!!!

[lucas]

Try this, it might be just what you need:

Option Explicit
Sub selectRange()
Dim rng As Range
'On Error Resume Next
Set rng = Application.InputBox("Select the target range with the mouse", Type:=8)
If rng Is Nothing Then Exit Sub
'On Error GoTo 0
rng.Value = 2
End Sub

[Saladsamurai]

Whoa!!! Lucas! That's absolutely PERFECT! I am in class right now, but I might post back just to fine tune it!

Thanks so much!!!

[mdmackillop]

On a Userform you want the RefEdit control

[domfootwear]

You can use the Userform to design the inputbox

Pls see the sample in the att.