Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: InputBox Method instead of RefEdit

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

    [ This Message was edited by: NateO on 2002-03-19 13:52 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-19 14:25 ]

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •