PDA

View Full Version : [SOLVED] Problem with Application.InputBox



Cyberdude
08-30-2005, 12:05 PM
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
08-30-2005, 12:16 PM
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
08-30-2005, 07:38 PM
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
08-30-2005, 08:04 PM
Since RG is a Range variable, you can just use


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

Cyberdude
08-31-2005, 11:37 AM
Thanx, Matt. That's what I was missing. http://vbaexpress.com/forum/images/smilies/notworthy.gif

mvidas
08-31-2005, 11:41 AM
Glad to help!