How to get Excel Userform to Validate then edit Userform

Question

I have created a Userform, and I am trying to get it to validate and check that all fields have been entered before it proceeds to enter the data onto the worksheet. So far I have got the code to check the fields and display an error message if one of the fields has no data.

I have tried to loop the validation by using the Call function, first to Data_Validation, and then to AddName_Click. Neither of them worked.

After the Userform is Initialised, the code then moves to the following Sub Routine

Private Sub AddName_Click()


'Variable Declaration
Dim BlnVal As Boolean

'Find Last Row on Staff Data Worksheet

Dim LastRow As Long
    Dim rng As Range

   'Use a range on the sheet
    Set rng = Sheets("Staff Data").Range("A2:E900")

    ' Find the last row
    LastRow = Last(1, rng)


     'Data Validation
    Call Data_Validation


    'Find Area value
    If ARLArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "ARL"
    If LSQArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "LSQ"
    If KNBArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "KNB"
    If RSQArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "RSQ"
    If RevenueControlInspectors = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "RCI"
    If SpecialRequirementTeam = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "SRT"

    rng.Parent.Cells(LastRow + 1, 2).Value = EmployeeNo1.Value
    rng.Parent.Cells(LastRow + 1, 3).Value = FirstName1.Value
    rng.Parent.Cells(LastRow + 1, 4).Value = LastName1.Value

    'Find Grade value
    If CSA2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSA2"
    If CSA1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSA1"
    If CSS2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSS2"
    If CSS1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSS1"
    If CSM2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSM2"
    If CSM1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSM1"
    If AM = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "AM"
    If RCI = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "RCI"
    If SRT = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "SRT"

  On Error GoTo ErrOccured
    'Boolean Value
    BlnVal = 0





ErrOccured:
    'TurnOn screen updating
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    'Empty Area
        ARLArea = False
        LSQArea = False
        KNBArea = False
        RSQArea = False
        RevenueControlInspectors = False
        SpecialRequirementTeam = False

    'Empty EmployeeNo1
        EmployeeNo1.Value = ""

    'Empty FirstName1
        FirstName1.Value = ""

    'Empty LastName1
        LastName1.Value = ""

    'Empty Grade
        CSA2 = False
        CSA1 = False
        CSS2 = False
        CSS1 = False
        CSM2 = False
        CSM1 = False
        AM = False
        RCI = False
        SRT = False

End Sub

as you can see I have added the rest of the routine above after it is supposed to go to the Data Validation routine to check all data has been entered. The Data Validation routine is shown below.

Sub Data_Validation()
' Check if all data has been entered on the userform

     If ARLArea = False And KNBArea = False And LSQArea = False And RSQArea = False And RevenueControlInspectors = False And SpecialRequirementTeam = False Then
        MsgBox "Select Area!", vbInformation, ("Area")
        ARLArea.SetFocus
        Exit Sub
        End If
     If EmployeeNo1 = "" Then
        MsgBox "Enter Employee Number!", vbInformation, ("Employee Number")
        EmployeeNo1.SetFocus
        Exit Sub
        End If
     If FirstName1 = "" Then
        MsgBox "Enter First Name!", vbInformation, ("First Name")
        FirstName1.SetFocus
        Exit Sub
        End If
     If LastName1 = "" Then
        MsgBox "Enter Last Name!", vbInformation, ("Last Name")
        LastName1.SetFocus
        Exit Sub
        End If
     If CSA2 = False And CSA1 = False And CSS2 = False And CSS1 = False And CSM2 = False And CSM1 = False And AM = False And RCI = False And SRT = False Then
        MsgBox "Select Grade!", vbInformation, ("Grade")
        CSA2.SetFocus
        Exit Sub
        End If

        BlnVal = 1

End Sub

My problem is once the message appears, and I click OK. the program continues to run and enters the exisiting data to the worksheet. What I want it to do is when the Error message appears, and OK is clicked, the userform becomes live again and can be edited with the missing data. Then I want it to validate the form again until all fields have been entered before it then transfers the data to the worksheet.

Mike · Accepted Answer

The reason this continues on is because when you exit this sub, its just ending the processing for the current sub not the additional code.

You either need to:

raise an error in the lower sub and handle the exception in your upper level sub
turn this Data_Validation() into a function that returns a value, such as 0 when no errors or 1 when errors exist
just move this large block of if's to the On_Click event that you're using to trigger the insert. If you move the code up to the main sub, the EXIT SUB will properly kick your code out after triggered. Then feed that value into your upper level sub.

The easiest to immediately implement would be turning Data_Validation() into a Function and returning a value, True or False if the validation completes.

If the validation fails, we'll process the error messages and return a FALSE value to the main Sub to exit the sub and then allow the user to update the form and click the button again. I'm not sure what your blnVal was for. Potentially trying to do what I've updated your code to do?-but the only way for that particularly version of logic to work is if you set the variables to public and its not considered good practice to do so.

Keep in mind, that if you want the user to be able to update data in the middle of code processing, this isn't really feasible. You could create input boxes instead of error boxes on popup that allow the user to enter those fields and continue processing code when the input a value and accept the input.

Private Sub AddName_Click()


'Variable Declaration
Dim BlnVal As Boolean

'Find Last Row on Staff Data Worksheet

Dim LastRow As Long
    Dim rng As Range

   'Use a range on the sheet
    Set rng = Sheets("Staff Data").Range("A2:E900")

    ' Find the last row
    LastRow = Last(1, rng)


     'Data Validation - returns FALSE if failed, True if success
    If Data_Validation() = False Then
        Exit Sub
    End If


    'Find Area value
    If ARLArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "ARL"
    If LSQArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "LSQ"
    If KNBArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "KNB"
    If RSQArea = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "RSQ"
    If RevenueControlInspectors = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "RCI"
    If SpecialRequirementTeam = True Then rng.Parent.Cells(LastRow + 1, 1).Value = "SRT"

    rng.Parent.Cells(LastRow + 1, 2).Value = EmployeeNo1.Value
    rng.Parent.Cells(LastRow + 1, 3).Value = FirstName1.Value
    rng.Parent.Cells(LastRow + 1, 4).Value = LastName1.Value

    'Find Grade value
    If CSA2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSA2"
    If CSA1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSA1"
    If CSS2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSS2"
    If CSS1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSS1"
    If CSM2 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSM2"
    If CSM1 = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "CSM1"
    If AM = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "AM"
    If RCI = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "RCI"
    If SRT = True Then rng.Parent.Cells(LastRow + 1, 5).Value = "SRT"

  On Error GoTo ErrOccured
    'Boolean Value
    BlnVal = 0





ErrOccured:
    'TurnOn screen updating
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    'Empty Area
        ARLArea = False
        LSQArea = False
        KNBArea = False
        RSQArea = False
        RevenueControlInspectors = False
        SpecialRequirementTeam = False

    'Empty EmployeeNo1
        EmployeeNo1.Value = ""

    'Empty FirstName1
        FirstName1.Value = ""

    'Empty LastName1
        LastName1.Value = ""

    'Empty Grade
        CSA2 = False
        CSA1 = False
        CSS2 = False
        CSS1 = False
        CSM2 = False
        CSM1 = False
        AM = False
        RCI = False
        SRT = False

End Sub

-

Function Data_Validation() As Boolean 'Declare Function with Bool as data type

'Default True. False if any conditions met. When a function is called, a new variable,
'with the function name and datatype given is created.  You'll set the value in the
'function.  When the function ends either in Exit Function or
'End Function, whatever is contained in this variable is returned as the Functions result
    Data_Validation = True
' Check if all data has been entered on the userform



     If ARLArea = False And KNBArea = False And LSQArea = False And RSQArea = False And RevenueControlInspectors = False And SpecialRequirementTeam = False Then
        MsgBox "Select Area!", vbInformation, ("Area")
        ARLArea.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If EmployeeNo1 = "" Then
        MsgBox "Enter Employee Number!", vbInformation, ("Employee Number")
        EmployeeNo1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If FirstName1 = "" Then
        MsgBox "Enter First Name!", vbInformation, ("First Name")
        FirstName1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If LastName1 = "" Then
        MsgBox "Enter Last Name!", vbInformation, ("Last Name")
        LastName1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If CSA2 = False And CSA1 = False And CSS2 = False And CSS1 = False And CSM2 = False And CSM1 = False And AM = False And RCI = False And SRT = False Then
        MsgBox "Select Grade!", vbInformation, ("Grade")
        CSA2.SetFocus
        Data_Validation = False
        Exit Function
        End If

        BlnVal = 1


End Function

How to get Excel Userform to Validate then edit Userform

Answers (1)

Related Questions