CODEWITHSUNDEEP
excelvba
Tee
Tee

Reputation: 21

Code for making all fields on a UserForm required to be filled in before form submission

How can I make all fields on Excel VBA UserForm required/mandatory before it is submitted and data is entered into the corresponding worksheet?

UserForm Initialize code is below.

Private Sub UserForm_Initialize() ' don't change this.
         
    TPFORM.MultiPage.Value = 0
    
    Call ComboBoxValues.ComboBoxes_Items
    
End Sub

I am using an image instead of commandbutton and code of adding the data is below.

Private Sub ADDPROPERTYIMAGE2_Click()

    'TO ADD DATA IN TEXT FIELDS OF ADD PROPERTY SECTION OF THE FORM

    Dim intC As Integer
    Dim intB As Integer
    Dim Ctrl As Control      ' CREATE A CONTROL OBJECT.


    intC = addproperty.Range("A2").Value
    intB = addproperty.Range("F2").Value

    If TextBoxPROPRef.Value = "" Then
        MsgBox "A REFERENCE MUST BE ASSIGNED TO THE PROPERTY WHEN ENTERING PROPERTY DETAILS. THIS FIELD CANNOT BE BLANK", vbInformation, "THY PROPERTIES"
        Exit Sub
    End If


    addproperty.Range("A" & intC + 4).Value = TextBoxPROPRef.Value
    addproperty.Range("B" & intC + 4).Value = TextBoxPROPDoor.Value
    addproperty.Range("C" & intC + 4).Value = TextBoxPROPStreet.Value
    addproperty.Range("D" & intC + 4).Value = TextBoxPROPTown.Value
    addproperty.Range("E" & intC + 4).Value = TextBoxPROPPostcode.Value
    addproperty.Range("I" & intC + 4).Value = TextBoxPROPAVALDate.Value
    addproperty.Range("L" & intC + 4).Value = TextBoxPROPRent.Value
    addproperty.Range("W" & intC + 4).Value = TXTPROPNotes.Value


    'TO ADD DATA IN COMBOBOX FIELDS OF ADD PROPERTY SECTION OF THE FORM

    addproperty.Range("F" & intB + 4).Value = ComboBoxPROPStatus.Value
    addproperty.Range("G" & intB + 4).Value = ComboBoxPROPType.Value
    addproperty.Range("H" & intB + 4).Value = ComboBoxPROPFee.Value
    addproperty.Range("J" & intB + 4).Value = ComboBoxPROPVIEWARNG.Value
    addproperty.Range("K" & intB + 4).Value = ComboBoxPROPTenantType.Value
    addproperty.Range("M" & intB + 4).Value = ComboBoxPROPLRoom.Value
    addproperty.Range("N" & intB + 4).Value = ComboBoxPROPBRoom.Value
    addproperty.Range("O" & intB + 4).Value = ComboBoxPROPKitchen.Value
    addproperty.Range("P" & intB + 4).Value = ComboBoxPROPBathroom.Value
    addproperty.Range("Q" & intB + 4).Value = ComboBoxPROPGarden.Value
    addproperty.Range("R" & intB + 4).Value = ComboBoxPROPOfferedas.Value
    addproperty.Range("S" & intB + 4).Value = ComboBoxEICR.Value
    addproperty.Range("T" & intB + 4).Value = ComboBoxGAS.Value
    addproperty.Range("U" & intB + 4).Value = ComboBoxEPC.Value
    addproperty.Range("V" & intB + 4).Value = ComboBoxLICENSE.Value



    ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Ctrl.Value = ""
        End If
        If TypeName(Ctrl) = "ComboBox" Then
            Ctrl.Value = ""
        End If
    Next Ctrl
        
    ThisWorkbook.Save
            
End Sub

At the moment when image is clicked it functions as commandbutton and data is entered into the sheet successfully but all of the txtboxes and comboboxes must be filled/selected a value before the data goes to worksheet. This is the predicament.

The form is based on Multipage which holds 8 pages. When an image/button is clicked the correct sheet becomes active in the background.

Upvotes: 2

Views: 63

Answers (2)

TinMan
TinMan

Reputation: 7759

Assuming that the controls in ADDPROPERTYIMAGE2_Click are all the controls that we need to validate:

Function ValidateControls() As Boolean
    Dim ControlArray As Variant

    ControlArray = Array( _
        TextBoxPROPRef, TextBoxPROPDoor, TextBoxPROPStreet, TextBoxPROPTown, TextBoxPROPPostcode, _
        TextBoxPROPAVALDate, TextBoxPROPRent, TXTPROPNotes, _
        ComboBoxPROPStatus, ComboBoxPROPType, ComboBoxPROPFee, ComboBoxPROPVIEWARNG, _
        ComboBoxPROPTenantType, ComboBoxPROPLRoom, ComboBoxPROPBRoom, ComboBoxPROPKitchen, _
        ComboBoxPROPBathroom, ComboBoxPROPGarden, ComboBoxPROPOfferedas, ComboBoxEICR, _
        ComboBoxGAS, ComboBoxEPC, ComboBoxLICENSE)
    Dim Item
    For Each Item In ControlArray
        If Len(Item.Value) = 0 Then Exit Function
    Next
    
    ValidateControls = True
End Function

Alternatively, we can iterate over all the controls:

Function ValidateControls() As Boolean
    Dim Item As MSForms.Control
    For Each Item In Me.Controls
        Select Case TypeName(Item)
            Case "TextBox", "CheckBox"
                If Len(Item.Value) = 0 Then Exit Function
            Case "ComboBox", "ListBox"
                If Item.ListIndex = -1 Then Exit Function
        End Select
    Next
    ValidateControls = True
End Function

Complete Example:

Option Explicit

Private Sub ADDPROPERTYIMAGE2_Click()
    If Not ValidateControls Then
        MsgBox "Please ensure all required fields are filled out before proceeding.", vbExclamation, "Validation Error"
        Exit Sub
    End If

    'TO ADD DATA IN TEXT FIELDS OF ADD PROPERTY SECTION OF THE FORM

    Dim intC As Long
    Dim intB As Long
    Dim Ctrl As Control      ' CREATE A CONTROL OBJECT.


    intC = addproperty.Range("A2").Value
    intB = addproperty.Range("F2").Value

    If TextBoxPROPRef.Value = "" Then
        MsgBox "A REFERENCE MUST BE ASSIGNED TO THE PROPERTY WHEN ENTERING PROPERTY DETAILS. THIS FIELD CANNOT BE BLANK", vbInformation, "THY PROPERTIES"
        Exit Sub
    End If


    addproperty.Range("A" & intC + 4).Value = TextBoxPROPRef.Value
    addproperty.Range("B" & intC + 4).Value = TextBoxPROPDoor.Value
    addproperty.Range("C" & intC + 4).Value = TextBoxPROPStreet.Value
    addproperty.Range("D" & intC + 4).Value = TextBoxPROPTown.Value
    addproperty.Range("E" & intC + 4).Value = TextBoxPROPPostcode.Value
    addproperty.Range("I" & intC + 4).Value = TextBoxPROPAVALDate.Value
    addproperty.Range("L" & intC + 4).Value = TextBoxPROPRent.Value
    addproperty.Range("W" & intC + 4).Value = TXTPROPNotes.Value


    'TO ADD DATA IN COMBOBOX FIELDS OF ADD PROPERTY SECTION OF THE FORM

    addproperty.Range("F" & intB + 4).Value = ComboBoxPROPStatus.Value
    addproperty.Range("G" & intB + 4).Value = ComboBoxPROPType.Value
    addproperty.Range("H" & intB + 4).Value = ComboBoxPROPFee.Value
    addproperty.Range("J" & intB + 4).Value = ComboBoxPROPVIEWARNG.Value
    addproperty.Range("K" & intB + 4).Value = ComboBoxPROPTenantType.Value
    addproperty.Range("M" & intB + 4).Value = ComboBoxPROPLRoom.Value
    addproperty.Range("N" & intB + 4).Value = ComboBoxPROPBRoom.Value
    addproperty.Range("O" & intB + 4).Value = ComboBoxPROPKitchen.Value
    addproperty.Range("P" & intB + 4).Value = ComboBoxPROPBathroom.Value
    addproperty.Range("Q" & intB + 4).Value = ComboBoxPROPGarden.Value
    addproperty.Range("R" & intB + 4).Value = ComboBoxPROPOfferedas.Value
    addproperty.Range("S" & intB + 4).Value = ComboBoxEICR.Value
    addproperty.Range("T" & intB + 4).Value = ComboBoxGAS.Value
    addproperty.Range("U" & intB + 4).Value = ComboBoxEPC.Value
    addproperty.Range("V" & intB + 4).Value = ComboBoxLICENSE.Value

    ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Ctrl.Value = ""
        End If
        If TypeName(Ctrl) = "ComboBox" Then
            Ctrl.Value = ""
        End If
    Next Ctrl
        
    ThisWorkbook.Save
            
End Sub

Function ValidateControls() As Boolean
    Dim Item As MSForms.Control
    For Each Item In Me.Controls
        Select Case TypeName(Item)
            Case "TextBox", "CheckBox"
                If Len(Item.Value) = 0 Then Exit Function
            Case "ComboBox", "ListBox"
                If Item.ListIndex = -1 Then Exit Function
        End Select
    Next
    ValidateControls = True
End Function

Private Sub UserForm_Initialize() ' don't change this.
         
    TPFORM.MultiPage.Value = 0
    
    Call ComboBoxValues.ComboBoxes_Items
    
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

I would not recommend using Image as a Button. The main problem with image is that you can't use keyboard to tab through it. You are then mouse dependant.

Now comes the part where you want to ensure every textbox and combobox is filled. Use the same loop that you are using at the end of the code...

Private Sub ADDPROPERTYIMAGE2_Click()
    Dim Ctrl As Control
    Dim PageIndex As Integer
    Dim ParentMultiPage As MultiPage
    
    '~~> Loop through all controls in the form
    For Each Ctrl In Me.Controls
        '~~> Check type of control
        Select Case TypeName(Ctrl)
            Case "TextBox"
                '~~> Check if the textbox is empty
                If Len(Trim(Ctrl.Text)) = 0 Then
                    '~~> Check if the TextBox is inside a MultiPage
                    If TypeName(Ctrl.Parent) = "Page" Then
                        PageIndex = Ctrl.Parent.Index
                        
                        Set ParentMultiPage = Ctrl.Parent.Parent
                        '~~> Activate the correct page
                        ParentMultiPage.Value = PageIndex
                    End If
                    
                    '~~> Set focus on the textbox
                    Ctrl.SetFocus
                    MsgBox "All textboxes need to have a value"
                    Exit Sub
                End If
            Case "ComboBox"
                '~~> Check if the ComboBox is empty
                If Ctrl.ListIndex = -1 Then
                    '~~> Check if the ComboBox is inside a MultiPage
                    If TypeName(Ctrl.Parent) = "Page" Then
                        PageIndex = Ctrl.Parent.Index
                        
                        Set ParentMultiPage = Ctrl.Parent.Parent
                        '~~> Activate the correct page
                        ParentMultiPage.Value = PageIndex
                    End If
                    
                    '~~> Set focus on the ComboBox 
                    Ctrl.SetFocus
                    MsgBox "All ComboBoxes need to have a value"
                    Exit Sub
                End If
        End Select
    Next Ctrl

    '~~> Rest of your code
End Sub

Upvotes: 3

Related Questions