Inputbox question

[Wylie C]

I have two questions.
The Set rngActive = Application...line below executes without a problem. I
get the inputbox and can select the cell (may be on another worksheet). The
rngActive.Select line gives me an error message: Select method or range class
failed. If I use a Msgbox to display rngActive after the Set statement, it
does not display the inputbox cell address that displayed in the inutbox.

Dim rngActive As Range
Set rngActive = Application.InputBox(prompt:="Click cell or range",
Title:="Cell(s)", Type:=8)
rngActive.Select

The second question is why does the inputbox syntax below return an error
message.
I get an error message that select method of range class failed & the
Type:=8 causing the error. Why does the Type: statement work in the code
above and the one below displays an error?

rngActive = InputBox(prompt:="Click cell or range", Title:="Cell(s)",
Type:=8)

 

[JE McGimpsey]

FIrst, you can't select a range on a non-selected worksheet. Either
Select the parent of the range first or use Application.GoTo

Second, in the first instance, you're using XL's Inputbox method (i.e.,
Application.InputBox). In the second instance, you're using VBA's
InputBox method, which doesn't have a Type argument.

Take a look at the syntaces in Help.

 

[Wylie C]

Thanks. Followup question. I put the following code immediately following the
Set statement.
MsgBox (rngActive)
and get no cell reference when the code is run. The msgbox only displays the
OK button. How can I see the returned value from the inputbox?

 

[Dave Peterson]

Maybe the cell was empty???

You may want to watch out for cancelling, too.

dim rngActive as range
set rngActive = nothing
on error resume next
set rngActive = application.inputbox(prompt:="Click cell or range", _
Title:="Cell(s)", Type:=8)
on error goto 0

if rngActive is nothing then
msgbox "User hit cancel"
else
msgbox rngactive.cells(1).value
end if

If the user selected multiple cells, then
msgbox rngactive.value
would cause an error