Prompting a Cell Selection Dialog Box

[Guest]

Hello Everyone:

I am emailing this question from work, so I apologize I did not have the
opportunity to do a through search for answer through the forum. I did a
quick one but did not find my answer and thus this post. Thanks in advance
for any assistance.

I am writing a VBA macro in which I would like to have a cell selection
dialog to pop up for the user. I do not know if this is the correct name for
what I am looking for, but you can see this when you use the Function Wizard
in Excel or when you make a PivotTable and Excel prompts you for data range.
Any quick leads would be all I need. It usually looks like a Text Box
control with a little button at the right side which when you click it you
can navigate the spreadsheet to select your cell or range or cells.

Any leads would be great or even a listing of the method on an object to
generate this box (if possible) would be great. Again, thanks in advance.

Amar

 

[Frank Kabel]

Hi
- create a userform
- use a refEdit control for allowing cell selection (VBA's help should
provide more infos about this control)

This control can be found in the toolbox toolbar

 

[Dave Peterson]

A quick and dirty way to get a range is to use application.inputbox:


sub testme()

dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(prompt:="hi there!",type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
else
'you have a range
end if

End sub

 

[Guest]

Frank,
Thanks..excellent lead. I will experiment with this control which I
honestly never knew existed in my control toolbox until your post. Anyway,
thanks.

 

[Guest]

Dave,

Thank you so much. I have the code now in my Personal.xls to experiment and
learn from. I never knew this even existed in the InputBox method. Amazing.
Anyway, I'll use your and Frank's leads and figure out the best method for
my application. Thanks again for the lead and the code.

 

[Dave Peterson]

There is a difference between application.inputbox and plain old inputbox.

Type:=# is only available in application.inputbox().