Userform Resubmission

[everyColorDumb]

Hello there!

Currently, I have a Userform with a textfield that is validated once the "Submit" button is clicked. There are two possible outcomes: 1) The textfield value is valid and the form closes. 2) The textfield is not valid, and the user must continue filling or editing the form. Scenario 2 is where my bug lies. My intention with the 'Do...Loop Until isClicked = True' line was to have the program not check values until the user has clicked "Submit" again. Otherwise, it would be in this infinite loop of generating the error MsgBox and never actually give the user a chance to edit that field.


P.S This is my first post here, so please feel free to provide feedback on best practices or how I can better layout the problem. Many thanks in advance!!!

Dim isClicked As Boolean
Dim isValid As Boolean

Private Sub UserForm_Initialize()
'Set Submit button as unclicked
isClicked = False

'Empty BarcodeTextBox
BarcodeTextBox.Value = ""

'Empty Conc Unit ComboBox
ConcUnitComboBox.Clear

'Empty Vol Unit ComboBox
VolUnitComboBox.Clear

'Fill CityListBox
With ConcUnitComboBox
    .AddItem "ug/mL"
    .AddItem "ug/uL"
    .AddItem "mg/mL"
End With


'Fill VolUnitListBox
With VolUnitComboBox
    .AddItem "uL"
    .AddItem "mL"
    .AddItem "L"
End With

End Sub

Private Sub SubmitButton_Click()
    isClicked = True
    Debug.Print "Submit Button clicked --> isClicked Value: ", isClicked
    
    Dim NumDestPlates As Integer
    Dim isValid As Boolean: isValid = False
    Dim BarcodeTextString As String
    Dim BarcodeArray() As String
    Dim NumBarcodes As Integer
    Dim LandingSheet As Worksheet
    Dim RunSheet As Worksheet
    
    Set LandingSheet = ThisWorkbook.Sheets("FormSheet")
    Set RunSheet = ThisWorkbook.Sheets("Run Info")   
    
    Call CheckForm(RunSheet, LandingSheet)
    
    'Will only proceed to this if validated'
    Unload Me   
    
End Sub

Private Sub CheckForm(ByRef RunInfo As Worksheet, ByRef FormInfo As Worksheet)
    
    Dim formImport As New UserForm2
    Dim isComplete As Boolean: isComplete = False
    Dim BarcodeTextString As String
    
    Do
    
    CheckBeginning:
        Debug.Print "Checking the form..."

        BarcodeTextString = CStr(BarcodeTextBox.Value)

        'Remove all whitespaces in Barcode Input and covert all chars to uppercase
        BarcodeTextString = UCase(BarcodeTextString)
        BarcodeTextString = Replace(BarcodeTextString, " ", "")

        'Parse Barcode String
        BarcodeArray = Split(BarcodeTextString, ",")

        'Activate Runsheet
        RunInfo.Activate

        'Check that number of submitted barcodes equals number of destination plates
        NumDestPlates = WorksheetFunction.Max(Range("I3", Range("I3").End(xlDown)))
        UserNumBarcodes = UBound(BarcodeArray) - LBound(BarcodeArray) + 1

         'Number of user input barcodes must match number of dest plates
         If NumDestPlates = UserNumBarcodes Then
            isValid = True
            Debug.Print "isValid:", isValid
            MsgBox ("Number of barcodes match!")
            Exit Do
        Else
            isClicked = False
            Debug.Print "isClicked Value: ", isClicked
            MsgBox ("Number of barcodes don't match! Please check and try again.")
            BarcodeTextBox.SetFocus

            Debug.Print "isClicked Value: ", isClicked
            Do
                'Don't do anything until user clicks Submit button again
            Loop Until isClicked = True

            GoTo CheckBeginning               

        End If
    
    Loop Until isComplete = True 

End Sub

 

[dmt32]

Hi
welcome to forum

In such cases your Sub would be a Function that returns a boolean value (True / False) that would determine how your code in the calling procedure proceeds.

Not tested but see if this update to your code helps

Function IsComplete(ByVal FormImport As Object, ByVal RunInfo As Worksheet) As Boolean
    Dim NumDestPlates       As Long, UserNumBarcodes As Long
    Dim BarcodeTextString   As String, BarcodeArray As String
    
CheckBeginning:
    Debug.Print "Checking the form..."
    
    BarcodeTextString = CStr(FormImport.BarcodeTextBox.Value)
    
    'Remove all whitespaces in Barcode Input and covert all chars to uppercase
    BarcodeTextString = UCase(BarcodeTextString)
    BarcodeTextString = Replace(BarcodeTextString, " ", "")
    
    'Parse Barcode String
    BarcodeArray = Split(BarcodeTextString, ",")
    
    'Check that number of submitted barcodes equals number of destination plates
    NumDestPlates = WorksheetFunction.Max(RunInfo.Range("I3", RunInfo.Range("I3").End(xlDown)))
    UserNumBarcodes = UBound(BarcodeArray) - LBound(BarcodeArray) + 1
    
    'Confirm Number of user input barcodes match number of dest plates
    IsComplete = CBool(NumDestPlates = UserNumBarcodes)
    
    If Not IsComplete Then
        Debug.Print "Not Matched..."
        MsgBox "Number of barcodes don't match!" & Chr(10) & _
               "Please check And try again.", 48, "Not Matched"
        FormImport.BarcodeTextBox.SetFocus
     Else
        Debug.Print "All Match..."
    End If
    
End Function

and in the calling code

    Set RunSheet = ThisWorkbook.Sheets("Run Info")
    
    If Not IsComplete(Me, RunSheet) Then Exit Sub
    
    'Will only proceed to this if validated'
    Unload Me

Hope Helpful

Dave

 

[everyColorDumb]

Hi Dave!

Thank you, your response brought me one big step closer!!! For one, you corrected my mistake of equating subs and functions. Sorry, this is my first VB script. I was able to wrap that calling code inside a Do-While loop such that the user can resubmit the form instead of closing the form regardless of whether the validator function returns True/False. Also, I closed the form within the function's Else block for the case where it does pass.

Happy Learning!
Carissa

 

[dmt32]

Glad suggestion helped & appreciate feedback

Dave