How to get user to select cell

[CLR]

Hi all.........

I would like to select a sheet (no problem there),............ then I would
like a msgbox to pop up telling the user to select a cell (no problem
there),.......... then I would like for the macro to pause until the user to
selects a cell.......and then for the macro to continue to run using that
cell as the selection.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[FSt1]

hi
you could use a input box instead and have them enter the cell address.

Dim stg As String
stg = InputBox("enter cell address")
MsgBox stg 'for test only
Range(stg).Select 'for test only

regards
FSt1

 

[CLR]

Thank you, but no.......in this instance I want the user to select a
cell........

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Dim myCell as range
set mycell = nothing
on error resume next 'cancel will cause an error
set mycell = application.inputbox(Prompt:="select a cell", type:=8).cells(1)
on error goto 0

if mycell is nothing then
msgbox "You didn't select a cell!"
else
msgbox "You selected: " & mycell.address(external:=true)
end if

 

[CLR]

Got it Dave, and it does me fine...........thank you most kindly.......

BTW.......what does the Type:=8 mean, and are there other choices?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I hope that there are other choices--or what the heck was MS thinking when
starting with type:=8. <vbg>

Type:=8 means that you want to return a range.

If you search VBA's help for inputbox method (not inputbox function), you'll
see:

Type Optional Variant. Specifies the return data type. If this argument is
omitted, the dialog box returns text. Can be one or a sum of the following
values.

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values

You can use the sum of the allowable values for Type. For example, for an input
box that can accept both text and numbers, set Type to 1 + 2.

 

[CLR]

I guess that's why you get the Big Bucks, Dave <g>

Thanks most kindly,
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

When BillG, WarrenB and go out, I always pick up the tab!

I guess that's why you get the Big Bucks, Dave <g>

Thanks most kindly,
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Something ate my I.

Ouch!!!!!!!!

When BillG, WarrenB and go out, I always pick up the tab!