Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repeating Input on error message (Excel 2000 (S1)

    Is it possible to have an input box re-request an input if an error occurs? For example, I have 13 possible valid input values, and, depending on the input an action occurs. What if somewone enters an invalid entry? I currently get an error message and the program stops and needs to be debugged. If there is an invalid entry can the program detect this and repeat the input box entry request? Many thanks.

    Farmer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    The Application object in Excel has an improved version of InputBox. It has an extra argument Type. According to the online help:<blockquote><hr>Type:

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    If you know all the valid values at design-time, you could do as follows
    <pre>Dim bValid As Boolean

    bValid = False
    Do while not bValid
    Select Case InputBox(xxx,xxx,xxx etc)
    Case Valid1, Valid2, Valid3...
    bValid = True
    Case "" 'In case user cancels.
    bvalid = False 'So you can test the value later.
    Exit Do
    Case Else
    bValid = False
    End Select
    Loop
    If bValid Then
    .... (whatever you want to do with the supplied info)
    EndIf</pre>


    This will cause the input box to keep appearing until the user either cancels or enters a valid response.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    RMittelman/HansV many thanks for the suggestions - much appreciated. HansV , I'm not sure I am typing the correct syntax is it Type:= 1 etc? Thanks.

    F

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    Type application.inputbox somewhere in a procedure or in the Immediate window in the Visual Basic Editor, then press F1. The online help will explain the syntax and provide an example.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    Rmittelman,

    This is the routine I typed in:
    Dim bValid As Boolean
    Segment = InputBox(" Enter the Segment code Acronym", Xpos:=4500, Ypos:=2000)
    bValid = False
    Do While Not bValid
    Select Case InputBox(R_SSU, R_SC, R_RW, O_CITY, O_MIDT, O_WEND, O_ROSE, O_ROUK, O_BUSPARK, I_SE, I_ROUK, I_DIST, R_ALL, O_ALL, I_ALL)
    Case R_SSU, R_SC, R_RW, O_CITY, O_MIDT, O_WEND, O_ROSE, O_ROUK, O_BUSPARK, I_SE, I_ROUK, I_DIST, R_ALL, O_ALL, I_ALL
    bValid = True
    Case "" 'In case user cancels.
    bValid = False 'So you can test the value later.
    Exit Do
    Case Else
    bValid = False
    End Select
    Loop
    If bValid Then GoTo 50
    End If
    50 Application.ScreenUpdating = False

    When I run it a compile error message with the 'InputBox' part in Select Case InputBox highlighted. The reported message is:

    "Wrong number of arguments or invalid assignment"

    Not sure what's wrong here. Thanks.

    F

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    InputBox(R_SSU, ...) is not valid. The code should look like this:

    Dim bValid As Boolean
    Do While Not bValid
    Segment = InputBox(" Enter the Segment code Acronym", Xpos:=4500, Ypos:=2000)
    Select Case Segment
    Case R_SSU, R_SC, R_RW, O_CITY, O_MIDT, O_WEND, O_ROSE, O_ROUK, _
    O_BUSPARK, I_SE, I_ROUK, I_DIST, R_ALL, O_ALL, I_ALL
    bValid = True
    Case "" 'In case user cancels.
    bValid = False 'So you can test the value later.
    Exit Do
    Case Else
    bValid = False
    End Select
    Loop

    I don't know what the last lines in your code mean, but you probably posted only part of a larger procedure. However, I never use line numbers as labels any more. Reminds me of the 1980's.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repeating Input on error message (Excel 2000 (S1)

    Gotos are much older than the 80s and have been frowned upon for years whether line numbers are used or not. Debugging is MUCH easier when a structured approach is used and you don't have gotos sprinkled all over to confuse things.

    Can you use argument names like that in InputBox without using Prompt:= as well?
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    No, I admit I just copied the instruction, I didn't test it. Thanks for spotting this. For others reading this: you must either use named arguments, in which case the order is not important:
    Segment = InputBox(Prompt:="Enter the Segment code Acronym", XPos:=4500, YPos:=2000)
    or (arguments in a different order)
    Segment = InputBox(XPos:=4500, YPos:=2000, Prompt:="Enter the Segment code Acronym")
    or supply unnamed arguments in the exact order they are listed in the help file and in the Object Browser:
    Segment = InputBox("Enter the Segment code Acronym", , , 4500, 2000)
    The extra commas are for omitted arguments.

  10. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    Hans, I'm in an endless loop. Irrespective of whether I make a valid entry or not, the Input Box keeps popping up. Here is the code - copied and pasted:

    Do While Not bValid
    Segment = InputBox(" Enter the Segment code Acronym", Xpos:=4500, Ypos:=2000)
    Select Case Segment
    Case R_SSU, R_SC, R_RW, O_CITY, O_MIDT, O_WEND, O_ROSE, O_ROUK, _
    O_BUSPARK, I_SE, I_ROUK, I_DIST, R_ALL, O_ALL, I_ALL
    bValid = True
    Case "" 'In case user cancels.
    bValid = False 'So you can test the value later.
    Exit Do
    Case Else
    bValid = False
    End Select
    Loop

    F

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeating Input on error message (Excel 2000 (S1)

    I had assumed that R_SSU etc. are string variables containing the actual values. But perhaps they *are* the values. In that case, they should be enclosed in quotes:

    Case "R_SSU", "R_SC", ..., "I_ALL"

    (don't type the ..., that's laziness on my side)

Posting Permissions

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