InputBox Method instead of RefEdit

[DMeador52]

Rather than using the quirky RefEdit control on a form, I've elected to use the Application.Inputbox(...,8) method.

My question is this: If I use type 8 (returns a range object), why would the method return a Boolean False when "Cancel" is pressed??

This doesn't make sense to me. For example,

Set XRng = Application.InputBox("Select the X Range", "X-Range", , , , , ,

If the user selects a range and clicks "OK" then we're in business. But, if they click cancel, the method returns "False", clearly not a range object, and I get a type mismatch error for using the Set statement.

Am I missing something, or does the design of the method not make sense?

Thanks,

Doug

 

[NateO]

It stinks. Why set your range? e.g.,

xrng = Application.InputBox(prompt:="Select the X Range", Title:="X-Range", Type:=8)
If xrng<> False Then
'do some stuff
End If

Or if it need be set:

xrng2 = Application.InputBox(prompt:="Select the X Range", Title:="X-Range", Type:=8)
If xrng2<> False Then
Set xrng = xrng2
End If
'do stuff


_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-19 13:52

 

[DMeador52]

I agree on the 'It Stinks' statement. The reason I need to set the range is so that I'll have the actual address of the selection - e.g. Sheet1!A1:D55 - rather than the values in the range.

Anyway, thanks for the tip.

Doug

 

[NateO]

Need to add a 2nd step, see my 2nd procedure I added. Hope this helps.

 

[DMeador52]

Here's my code:

Private Sub cmdXVals_Click()

Dim XRng As Range

If Me.txtYVals.Text <> "" Then
If InStr(1, Me.txtYVals.Text, "!") <> 0 Then
ThisWorkbook.Worksheets(Left(Me.txtYVals.Text, _
InStr(1, Me.txtYVals.Text, "!") - 1)).Activate
End If
End If

OldLeft = Me.Left
OldTop = Me.Top
OldWidth = Me.Width
OldHeight = Me.Height

Me.Move 60, 10, 80, 10

XRng1 = Application.InputBox("Select the X Range", "X-Range", , , , , ,

Set XRng = Nothing

If XRng1 <> False Then
Set XRng = XRng1
Me.txtXVals = XRng.Worksheet.Name & "!" & XRng.Address
End If

Me.Move OldLeft, OldTop, OldWidth, OldHeight

Me.Repaint

End Sub

At this point:

Set XRng = Nothing

If XRng1 <> False Then
Set XRng = XRng1
Me.txtXVals = XRng.Worksheet.Name & "!" & XRng.Address
End If

Excel doesn't like 2 things. The test XRng1<>False fails with a Type mismatch. Also, if I comment that out, Set XRng=XRng1 fails with an "Object Required."

Am I doing something wrong?

Doug

 

[NateO]

Changed my mind to go to an errorhandler, nasty stuff, can't believe the old <>False is conking out on me (False = False!! Jeez Louise)....

Dim xrng As Range
On Error Resume Next
Set xrng = Application.InputBox(prompt:="Select the X Range", Title:="X-Range", Type:=8)
If xrng Is Nothing Then Exit Sub

 

[NateO]

Looks like Microsoft does it this way as well (pretty much):

http://www.microsoft.com/exceldev/tips/InBoxRng.htm

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-19 14:25

 

[DMeador52]

With a little prodding, it appears to have worked wonderfully.

I've been at this a while and I should've figured that out on my own. Thanks for all your time.

Doug