Problem with Application.InputBox

[Cyberdude]

I thought I read somewhere that when using Application.InputBox as a prompt to get a cell address that the user could just click on the cell on the worksheet and the address would magically appear in the input area. I've been trying to do that, and it works! Except that the value isn't passed to the variable it's assigned to. Consider the following:

Sub Test()
Dim Msg As String, StartAddr  As Variant
Msg = "Enter the address of the upper left" & vbCr & _
          " corner of the array:"
StartAddr=Application.InputBox(Msg,Type:=6)  '(2=Text + 4=Logical)
If StartAddr = False Or StartAddr = vbNullString Then GoTo Finish
'(other logic here)
Finish:
End Sub

When I try clicking the worksheet, the address appears in the input area, but the variable StartAddr gets a null string, as if nothing was filled in on the input area. If I type the address into the input area, it works correctly. What am I missing?

[mvidas]

Hi CD,

For application.inputbox, to return a range (like a RefEdit control), use Type:=8. Also, don't forget the set command.

Dim RG As Range
 On Error Resume Next
 Set RG = Application.InputBox("Please choose the range", Type:=8)
 On Error GoTo 0
 If RG Is Nothing Then
  Stop
 Else
  Stop
 End If

Matt

[Cyberdude]

Thanx for the reply, Matt. But I can't get it to work. Here's what I'm testing with using your suggestion:

Sub TestInputBox()
Dim RG As Range
On Error Resume Next
Set RG = Application.InputBox("Please choose the range", Type:=8)
On Error GoTo 0
If RG Is Nothing _
    Then MsgBox "RG is nothing" _
     Else Range(RG).Select
End Sub

I get run-time error 1004,
"Range of object_Global failed"
I tried both typing in a cell address (with quotes and without quotes), and clicking on the worksheet. One error message said I have to click the input box before I click on the worksheet, but that was no help. I got the same results. Obviously I have no idea what I'm doing.

[mvidas]

Since RG is a Range variable, you can just use

'Else Range(RG).Select
Else RG.Select

[Cyberdude]

Thanx, Matt. That's what I was missing.

[mvidas]

Glad to help!