Do While Loop using output from Input Box as criteria

[Jimmy P]

OK. I’m stuck.

I’m trying to do a “Do While Loop”. Desired results are:

1. Do While user input to InputBox “NewSize” <> False ‘ User hit Enter, OK or Cancel with no input
2. If no user input put up MsgBox 1
3. On OK, Loop back to entry point
4. On valid user input (anything => 0), exit Loop to next procedure

This is what’s happening.

1. Enters Loop and puts up Input Box “NewSize”
2. If user hits Enter, OK or Cancel without inputting valid data, puts up MsgBox 1. ‘ Correct
3. On hit OK in MsgBox 1, exits Loop to next proc. ‘ Does not Loop. - Not correct
4. If user inputs valid data for InputBox, puts up MsgBox 1. ‘ Not correct
   1. Should not put up MsgBox 1. Should exit Loop to next proc.
5. On click OK for MsgBx 1, exits Loop and steps into next Proc. – Not correct
   1. This is correct except it should not put MsgBox 1 in as it does in step 4.
   2. It should just exit the Loop to the next proc.

Code:

Do While NewSize = False ‘ I have tried = “”, < 0, > 0. = Null – No Joy
NewSize = InputBox("Enter the last Sample # you want " & vbNewLine & "to include in your sample set" & vbNewLine _
& vbNewLine & "If you got here by mistake:" & vbNewLine & vbNewLine & "then enter a '0' into the input box and Click 'OK' or press ‘Enter’ to use the entire Sample Data Set'" & vbNewLine & "Your must enter a value to proceed")
' MsgBox 1
MsgBox "You have not entered a value. Click 'OK' to Retry", vbOKOnly, "No Input"
Loop

 

[iliace]

I am posting my entire procedure that I used to test this out. First of all, define your NewSize as a Variant. This way, you can distinguish between a 0 and a False (in case of cancel). Secondly, use Application.InputBox; the default is VBA.InputBox, which is less robust. The Application.InputBox allows you to specify an entry type, so you can validate that the user entered a number in the first place.

Public Sub doNewSize()
  Dim NewSize As Variant
  Do While (TypeName(NewSize) = "Boolean" And NewSize = False) Or NewSize < 0 ' I have tried = “”, < 0, > 0. = Null – No Joy
    NewSize = Application.InputBox("Enter the last Sample # you want " _
          & vbNewLine & "to include in your sample set" & vbNewLine _
          & vbNewLine & "If you got here by mistake:" & vbNewLine _
          & vbNewLine & "then enter a '0' into the input box and Click 'OK' or press ‘Enter’ to use the entire Sample Data Set'" _
          & vbNewLine & "Your must enter a value to proceed", "New Size", Type:=1)
    Debug.Print NewSize, Not NewSize Or NewSize < 0, TypeName(NewSize)
    If (TypeName(NewSize) = "Boolean" And NewSize = False) Or NewSize < 0 Then
      MsgBox "You have not entered a value. Click 'OK' to Retry", vbOKOnly, "Bad Input"
    End If
  Loop
End Sub

This seems to work according to your requirements.

On a side note - I'm not sure whether this is your desired functionality, but it reads that way - when the users press Cancel, they are still returned to the input box with the error message. From the standpoint of interface, this is not a good practice. If the user hits cancel, you should abort your entire operation instead of prompting them for an input. Right now, the way I have it set up, it returns you to the input box whenever the user presses cancel.

 

[Jimmy P]

No Joy! Hitting Cancel jumps right out of the Loop. I like your suggestion about if Cancel hit, Abort. Makes a lot of sense so I'll go this way. Still need to Loop if OK or Enter hit without any data.

Here's the code surrounding yours so you can see what happens before and after.

CngSamSize = MsgBox("There are " & Cnt & " Total Samples" _
& vbNewLine & vbNewLine & "Click 'Yes to select a smaller sample Set'" _
& vbNewLine & vbNewLine & "Click 'No' to begin processing all Samples in the Data Set", vbYesNo, "Sample Count")

' Add code for If user selects No GoTo Calc and MsgBox all samples selected

If CngSamSize = vbNo Then

MsgBox "You have choosen to use all " & Cnt & " Samples" _
& vbNewLine & vbNewLine & "To Change your selection Click 'OK' and then" _
& vbNewLine & "Click the 'Select Data Sets' Button to start over selecting a smaller Sample set" _
& vbNewLine & vbNewLine & "To process all " & Cnt & " Samples click 'OK'" _
& vbNewLine & "and then Click the 'Load First Sample' Button", vbOKOnly + vbInformation, "Samples Selected"
End If

If CngSamSize = vbYes Then

Dim NewSize As Variant
Do While (TypeName(NewSize) = "Boolean" And NewSize = False) Or NewSize < 0
NewSize = Application.InputBox("Enter the last Sample # you want " _
& vbNewLine & "to include in your sample set" & vbNewLine _
& vbNewLine & "If you got here by mistake:" & vbNewLine _
& vbNewLine & "then enter a '0' into the input box and Click 'OK' or press ‘Enter’ to use the entire Sample Data Set'" _
& vbNewLine & "Your must enter a value to proceed", "New Size", Type:=1)
Debug.Print NewSize, Not NewSize Or NewSize < 0, TypeName(NewSize)
If (TypeName(NewSize) = "Boolean" And NewSize = False) Or NewSize < 0 Then
MsgBox "You have not entered a value. Click 'OK' to Retry", vbOKOnly, "Bad Input"
End If
Loop

I looked in the Immediate Window and there is nothing there from your Deug.Print statement

 

[mikerickson]

The VBA.InputBox can distinguish between Cancel, 0 and null string entry.

Dim uiEntry As String
uiEntry = InputBox("enter something")
If StrPtr(uiEntry) = 0 Then
    MsgBox "cancled"
Else
    If uiEntry = vbNullString Then
        MsgBox "null string entered"
    Else
        MsgBox "User entered " & uiEntry
    End If
End If

I agree with Illace that Cancel should take the user out of the procedure. But here is my attempt to do what you want.

Dim NewSize As String
NewSize = " "

Do

    If NewSize = vbNullString Then MsgBox "You have not entered a value. Click 'OK' to Retry", vbOKOnly, "No Input"
    
    NewSize = InputBox("Enter the last Sample # you want " & _
        vbNewLine & "to include in your sample set" & _
        vbNewLine & vbNewLine & "If you got here by mistake:" & _
        vbNewLine & vbNewLine & "then enter a '0' into the input box and Click 'OK' or press ‘Enter’ to use the entire Sample Data Set'" & _
        vbNewLine & "Your must enter a value to proceed")

Loop Until StrPtr(NewSize) <> 0

' is this the meaning of the user's entry.
If NewSize = "0" Then
    MsgBox "got here by mistake"
ElseIf NewSize = vbNullString Then
    MsgBox "use entire data set"
Else
    MsgBox "size entered is " & NewSize
End If

 

[mikerickson]

I was just thinking, you seem to want the use to either
press enter (i.e. enter vbNullString) to use the whole data set
Enter 0 - if they got there by mistake
Enter a positive integer - for the size.

The coding would be simpler if you just use "entire data set" as the default for the InputBox.

Dim uiSize As String
uiSize = " "

Do
    'If uiSize = vbNullString Then MsgBox "I KNOW BETTER THAN YOU. YOU MUST ENTER SOMETHING!"
    
    uiSize = InputBox("Enter a size" & vbCr & "or press Enter" & vbCr & "or 0 if you want out", Default:="use whole data set")
    If Val(uiSize) < 0 Then uiSize = vbNullString
    
Loop Until IsNumeric(uiSize) Or uiSize = "use whole data set"

 

[Jimmy P]

Mikerickson,

Your code is great and very efficient. I've tweaked the text and added an exit MsgBox to confirm with the user the new Sample size. If I knew how. It grabs a number from Cnt to test the exit MsgBox now. Here's the code.

Sub SetNewSize()

Dim uiSize As String
uiSize = " "
Cnt = 10 'comes from CntSamp procedure

Do
If uiSize = vbNullString Then MsgBox "You must enter a value or Click OK to keep all Samples", vbOKOnly, "Select Action"

uiSize = InputBox("Enter the last Sample # to process and Click OK" _
& vbCr & vbCr & "Click OK to keep all Samples", Default:="Entire Sample Data Set is selected")
If Val(uiSize) < 0 Then uiSize = vbNullString

Loop Until IsNumeric(uiSize) Or uiSize = "Entire Sample Data Set is selected"

MsgBox "The new Sample size is set to the first " & Cnt & " Samples", vbOKOnly, "Sample Size Set"

End Sub

Next ? How do I take the uiSize String and use it in the message box in place of Cnt to show the user the new Sample set size. Also, I want to pass that number to another procedure to update a counter.

 

[Jimmy P]

Mikerickson,

Figured it out. Used Val(uiSize)

Many thanks for your help.

Thread Completed