User input - issues with msgbox/inputbox and/or selectfocus

lordriel

Board Regular
Joined
Nov 1, 2005
Messages
68
Office Version
  1. 365
Platform
  1. Windows
Having a couple problems and nothing I've seen has given me a solution...hopefully someone out there can help me.

The code below is just a portion of the overall program (which involves at least five userforms and a multitude of objects), but is directly where my issue resides.

An explanation of the portion:

Basically, the user keys in a number - depending on the particular user, it may be either a 5-digit numeric or a 7-digit (the 5-digit # with two leading zeroes). Input must be 5 digit (without leading zeroes), or 7 digit (with leading zeroes only).

An explanation of the problems:

I've used both inputbox and msgbox to alert the user to an incorrect entry. With the former, they can input the correct number. However, at that point the input is outside the select process and there's no input error-trapping. Given that the input will most usually be correct on the re-entry, I do not like carte blanche input and would prefer to continue double-checking input data. All I can see to do is build more if-then loops with each re-entry - hopefully there's a less convoluted way?

With the latter (msgbox), upon response, the cursor moves to the next textbox. Since I'm attempting to simplify user input, making them back up via keyboard or mouse to correct their entry isn't effective. I've used setfocus as well as a couple other solutions offered within the board, but nothing has worked.

Hopefully my explanations are clear...and the solution(s) exists.

Thanks to all -

lr









Code:
Private Sub TextBox6_afterupdate()
Dim ordr, ordlen, ordopn As String
Dim Msg, Style, Title, Help, Ctxt, default, Response
Dim compapp
ordr = Trim(userform1.TextBox6.Text)


ordlen = Len(ordr)
ordopn = Left(ordr, 2)

Select Case ordlen
    Case 5
        If ordopn <> "00" Then
            ordr = "00" & ordr
            TextBox6.Text = ordr
        Else
            Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr             ' Set default.
            Response = InputBox(Message, Title, default)
            ordr = Response
            TextBox6.Text = ordr
        End If
    Case 7
        If ordopn <> "00" Then
            Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr    ' Set default.
            Response = InputBox(Message, Title, default)
            ordr = Response
            TextBox6.Text = ordr
            End If
            
    Case Else
            Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr    ' Set default.
            Response = InputBox(Message, Title, default)
            ordr = Response
            TextBox6.Text = ordr
End Select
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You could try to include a loop within each case,

e.g.
Select Case...
StartAgain
input your chere here
if error then goto StartAgain

Select Case....

If the user makes an error it will loop back to the input box until they finally get it right or click the "cancel" (it which case you need it to exit the sub)
 
Upvote 0
tactps said:
You could try to include a loop within each case,

e.g.
Select Case...
StartAgain
input your chere here
if error then goto StartAgain

Select Case....

If the user makes an error it will loop back to the input box until they finally get it right or click the "cancel" (it which case you need it to exit the sub)
You could try to include a loop within each case

I'm afraid that's what I'm going to have to do...was hoping that there was a less convoluted solution.

Thanks, tactps!

lr
 
Upvote 0
Perhaps this will work for you:

Place this Function in a Standard Module:
Code:
Function ValOrder(ByVal ordr As String) As String
    Dim Valid As Boolean
    
    Valid = False
    
    Do
        Select Case Len(ordr)
            Case 5
                If Left(ordr, 2) <> "00" Then
                    ordr = "00" & ordr
                    Valid = True
                End If
            Case 7
                If Left(ordr, 2) = "00" Then
                    Valid = True
                End If
        End Select
        
        If Not Valid Then
            ordr = InputBox("Incorrect Order Number", "Input Error", ordr)
        End If
        
    Loop Until Valid
    
    ValOrder = ordr
    
End Function
and this code in the UserForm:
Code:
Private Sub TextBox6_afterupdate()
    TextBox6 = ValOrder(TextBox6)
End Sub
 
Upvote 0
Or perhaps a change as small as this...
Code:
Private Sub TextBox6_afterupdate()
Dim ordr, ordlen, ordopn As String
Dim Msg, Style, Title, Help, Ctxt, default, Response
Dim compapp
ordr = Trim(UserForm1.TextBox6.Text)


ordlen = Len(ordr)
ordopn = Left(ordr, 2)

FromTheTop:
Select Case ordlen
    Case 5
        If ordopn <> "00" Then
            ordr = "00" & ordr
            TextBox6.Text = ordr
        Else
            Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr             ' Set default.
            Response = InputBox(Message, Title, default)
            ordr = Response
            TextBox6.Text = ordr
        End If
    Case 7
        If ordopn <> "00" Then
'           Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr    ' Set default.
            Response = InputBox(Message, Title, default)
            If Left(Response, 2) <> "00" Then
                GoTo FromTheTop
              Else:
                ordr = Response
                TextBox6.Text = ordr
            End If
        End If
    Case Else
            Message = "Incorrect Order Number"    ' Set prompt.
            Title = "Input Error"    ' Set title.
            default = ordr    ' Set default.
            Response = InputBox(Message, Title, default)
            ordr = Response
            TextBox6.Text = ordr
End Select

End Sub
Hope it helps.


[EDIT]
Looking at this again I see some weakness in the Case Else statement.
Looks to me like BJungheim's function is the shortest & sweetest solution.
 
Upvote 0
Thanks, BJungheim & HalfAce! Will run the code tomorrow (both sets) and will get back to you on the results.

Again, my thanks for the sharing of your respective expertise!

lr
 
Upvote 0
BJungheim, your solution worked beautifully! Many thanks for uncudgeling my brain!

Thanks to all that submitted!
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,332
Members
452,396
Latest member
ajl_ahmed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top