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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Amar Kapadia" schrieb im Newsbeitrag
...
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,
Thanks..excellent lead. I will experiment with this control which I
honestly never knew existed in my control toolbox until your post. Anyway,
thanks.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Amar Kapadia" schrieb im Newsbeitrag
...
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

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

Amar Kapadia wrote:

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

--

Dave Peterson

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" wrote:

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

Amar Kapadia wrote:

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

--

Dave Peterson

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

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



Amar Kapadia wrote:

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" wrote:

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

Amar Kapadia wrote:

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

--

Dave Peterson



--

Dave Peterson