Solved Getting Loop in Excel VBA to validate only certain textboxes

March 12, 2017 at 11:13:47
Specs: Windows 8.1
I have been able to make my Loop to look for Empty TextBoxes on my userform with
5 Tabs and it is working up to a fashion. Not as I want it to work. I have 23 textbox
to be completed. I have written individual code for each TextBox to validate but that
is a lot of code!!! thought a Loop will work better.

My help call is the following:

I am trying to change the ForeColor as well if the Textbox is empty string. The
Backcolor if the textbox is empty os working fine.
I am trying to SetFocus to the first textbox that has empty string. I think it has
something to do with the fact that I have 5 Tabs and a lot of different
Frames on the Tabs.

This is the code for the Loop.
I have also included some of the code which I originally wrote to do the validating for
each individual textbox. there are 23 of them.


PLEASE I need help seriously.


Private Function EverythingFilledIn() As Boolean
'CREATING FUNCTION TO STEAMLINE CODE TO USE DURING VALIDATION OF CONTROLS ON USERFORM
'MAKES CODE ESIER TO READ AND MORE FLEXIBLE TO MAKE CHANGES TO
'LOOP OVER ALL CONTROLS ON THE FORM INSTEAD OF ONE BY ONE

    Dim ctl As MSForms.Control
    'set variable that will look at every single Control on the form be it Text, Date, ComboBox etc use MSForms.Control as this will make certian that we always use contols from MSForms contol - to avoid duplication from other controls
    Dim txt As MSForms.TextBox
    'variable so thaty code can be made more robust and to make changes so that the intellisense will work
    Dim AnythingMissing As Boolean
    'to find if anything is missing in textbox to SetFocus to that textbox
    
    'assume everything on userform has been filled in for procedure to work properly
    EverythingFilledIn = True
    AnythingMissing = False
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            Set txt = ctl 'transfer what is in the ctl variable to txt variable - will make intellisece work as it should
            If txt.Value = "" Then 'check if Contols value is an empty string
                txt.BackColor = rgbHotPink 'if Contols value is an empty string change Backcolor to Pink
                'Controls(ctl.Name & "lbl").ForeColor = rgbFireBrick
                If Not AnythingMissing Then ctl.SetFocus
                AnythingMissing = True
                EverythingFilledIn = False
            End If
        End If
    Next ctl
     End Function

Private Function EverythingFilledIn() As Boolean
'CREATING FUNCTION TO STEAMLINE CODE TO USE DURING VALIDATION OF CONTROLS ON USERFORM
'MAKES CODE ESIER TO READ AND MORE FLEXIBLE TO MAKE CHANGES TO
'LOOP OVER ALL CONTROLS ON THE FORM INSTEAD OF ONE BY ONE

    Dim ctl As MSForms.Control
    'set variable that will look at every single Control on the form be it Text, Date, ComboBox etc use MSForms.Control as this will make certian that we always use contols from MSForms contol - to avoid duplication from other controls
    Dim txt As MSForms.TextBox
    'variable so thaty code can be made more robust and to make changes so that the intellisense will work
    Dim AnythingMissing As Boolean
    'to find if anything is missing in textbox to SetFocus to that textbox
    
    'assume everything on userform has been filled in for procedure to work properly
    EverythingFilledIn = True
    AnythingMissing = False
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            Set txt = ctl 'transfer what is in the ctl variable to txt variable - will make intellisece work as it should
            If txt.Value = "" Then 'check if Contols value is an empty string
                txt.BackColor = rgbHotPink 'if Contols value is an empty string change Backcolor to Pink
                'Controls(ctl.Name & "lbl").ForeColor = rgbFireBrick
                If Not AnythingMissing Then ctl.SetFocus
                AnythingMissing = True
                EverythingFilledIn = False
            End If
        End If
    Next ctl
    
    
 End Function


See More: Getting Loop in Excel VBA to validate only certain textboxes

Reply ↓  Report •

#1
March 13, 2017 at 06:37:22
✔ Best Answer
I'm not quite sure what your question is, i.e. what is working and what isn't.

Is the setting of the ForeColor the only problem? If so, perhaps the technique used here would work for you. Obviously it would need to be looped.

https://msdn.microsoft.com/en-us/li...

It's not clear to me if you are having problems getting the loop code to work. Without your workbook to test the code against, it's hard for me to tell what is working and what isn't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
March 13, 2017 at 16:26:14
Ok. Here goes.

I am not sure how to send you my whole workbook so that I can explain my question better. It will be easier to explain if there is a way to send you my whole workbook.
I have now included my whole Save code wherein my problem lies..

I have written code in the Private Function EverythingFilledIn() As Boolean to check if all TextBox fields are completed before user saves the form. It is working, meaning it is changing the colors of the background of texboxes and Labels as I want it to work.

My problems are the following:

Problem 1.

The Save Button is on the last Tab. If for instance the FIRST empty Textbox is on TaB 1 eg. txtDataSurname I want the cursor (SetFocus) to go directly to that empty TextBox. thereafter cursor must go to every subsequent empty TextBox until all is completed and user then clicks Save button save form.

Problem 2.

This is very long code. I am trying to make a loop to check through all textboxes in my code to make the code more robust and efficient.

Private Sub cmdSave_Click()
'TESTING IF A CONTROL/TEXTBOX IS EMPTY

 'IF ANY FIELD/CONTROL IS NOT FILLED IN THE THE SUB ROUTINE WILL NOT EXECUTE
 
    If Not EverythingFilledIn Then Exit Sub
 
'TESTING/CHECKING IF THERE ARE NO EMPTY CONTROLS ON USERFORM MUST BE DONE BEFORE RECORD IS SAVED TO DATABASE

    Me.Hide 'TO HIDE THE USERFORM DURING UPDATE/SAVE OF DATA TO ApplcationData WORKSEET

    Call SaveDataToList
    
'AFTER CODE IS RUN UNLOAD FOR AND GO BACK TO MENU TAB

   Unload Me

End Sub

Private Function EverythingFilledIn() As Boolean
'CREATING FUNCTION TO STEAMLINE CODE TO USE DURING VALIDATION OF CONTROLS ON USERFORM
'MAKES CODE ESIER TO READ AND MORE FLEXIBLE TO MAKE CHANGES TO
'LOOP OVER ALL CONTROLS ON THE FORM INSTEAD OF ONE BY ONE

    Dim ctl As MSForms.Control
    'set variable that will look at every single Control on the form be it Text, Date, ComboBox etc use MSForms.Control as this will make certian that we always use contols from MSForms contol - to avoid duplication from other controls
    Dim txt As MSForms.TextBox
    'variable so thaty code can be made more robust and to make changes so that the intellisense will work
    Dim AnythingMissing As Boolean
    'to find if anything is missing in textbox to SetFocus to that textbox
    
 If txtDataSurname.Value = "" Then 'check if - txtDataSurname - contains an empty string
    'if empty then change backgroud color and labelcolor

        txtDataSurname.BackColor = rgbHotPink
        lblDataSurname.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
        'txtDataSurname.SetFocus 'sets focus on the control.
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
   End If
        
     If txtDataInI.Value = "" Then 'check if - txtDataInI - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtDataInI.BackColor = rgbHotPink
        lblDataInI.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
        txtDataInI.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If
    
    If txtDataPlaceOfEnlistment.Value = "" Then 'check if - txtDataPlaceOfEnlistment - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtDataPlaceOfEnlistment.BackColor = rgbHotPink
        lblDataPlaceOfEnlistment.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
        txtDataPlaceOfEnlistment.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If
    
    If txtDataTownOfEnlistment.Value = "" Then 'check if - txtDataTownOfEnlistment - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtDataTownOfEnlistment.BackColor = rgbHotPink
        lblDataTownOfEnlistment.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
        txtDataTownOfEnlistment.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If
        
    If txtPersLastUnit.Value = "" Then 'check if - txtPersLastUnit - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersLastUnit.BackColor = rgbHotPink
        lblPersLastUnit.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersLastUnit.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersHAddr1.Value = "" Then 'check if - txtPersHAddr1 - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersHAddr1.BackColor = rgbHotPink
        lblPersHAddr1.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersHAddr1.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersHAddr2.Value = "" Then 'check if - txtPersHAddr2 - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersHAddr2.BackColor = rgbHotPink
        lblPersHAddr2.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersHAddr2.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersHCity.Value = "" Then 'check if - txtPersHCity - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersHCity.BackColor = rgbHotPink
        lblPersHCity.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersHCity.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersHPOBox.Value = "" Then 'check if - txtPersHPOBox - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersHPOBox.BackColor = rgbHotPink
        lblPersHPOBox.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersHPOBox.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersHPOCity.Value = "" Then 'check if - txtPersHPOCity - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersHPOCity.BackColor = rgbHotPink
        lblPersHPOCity.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersHPOCity.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersNOKSurname.Value = "" Then 'check if - txtPersNOKSurname - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersNOKSurname.BackColor = rgbHotPink
        lblPersNOKSurname.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersNOKSurname.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersNOKInI.Value = "" Then 'check if - txtPersNOKInI - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersNOKInI.BackColor = rgbHotPink
        lblPersNOKInI.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersNOKInI.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersNOKHAddr1.Value = "" Then 'check if - txtPersNOKHAddr1 - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersNOKHAddr1.BackColor = rgbHotPink
        lblPersNOKHAddr1.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersNOKHAddr1.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersNOKHAddr2.Value = "" Then 'check if - txtPersNOKHAddr2 - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersNOKHAddr2.BackColor = rgbHotPink
        lblPersNOKHAddr2.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersNOKHAddr2.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersNOKCity.Value = "" Then 'check if - txtPersNOKCity - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersNOKCity.BackColor = rgbHotPink
        lblPersNOKCity.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersNOKCity.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

     If txtPersEmployer.Value = "" Then 'check if - txtPersEmployer - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersEmployer.BackColor = rgbHotPink
        lblPersEmployer.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersEmployer.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

     If txtPersCivilianOccupation.Value = "" Then 'check if - txtPersCivilianOccupation - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersCivilianOccupation.BackColor = rgbHotPink
        lblPersCivilianOccupation.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersCivilianOccupation.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtPersCivilianOccupation.Value = "" Then 'check if - txtPersCivilianOccupation - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtPersCivilianOccupation.BackColor = rgbHotPink
        lblPersCivilianOccupation.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtPersCivilianOccupation.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtSAPDMemo.Value = "" Then 'check if - txtSAPDMemo - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtSAPDMemo.BackColor = rgbHotPink
        lblSAPDMemo.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 3 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtSAPDMemo.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtCHAAppMemo.Value = "" Then 'check if - txtCHAAppMemo - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtCHAAppMemo.BackColor = rgbHotPink
        lblCHAAppMemo.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtCHAAppMemo.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtCHAInterventionsMemo.Value = "" Then 'check if - txtCHAInterventionsMemo - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtCHAInterventionsMemo.BackColor = rgbHotPink
        lblCHAInterventionsMemo.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtCHAInterventionsMemo.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtCHADivision.Value = "" Then 'check if - txtCHADivision - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtCHADivision.BackColor = rgbHotPink
        lblCHADivision.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtCHADivision.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

    If txtCHAPost.Value = "" Then 'check if - txtCHAPost - contains an empty string
    'if empty then change backgroud color and labelcolor
        txtCHAPost.BackColor = rgbHotPink
        lblCHAPost.ForeColor = rgbFireBrick
        tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
        txtCHAPost.SetFocus 'sets focus on the control
        EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
    End If

End Function

'Private Function EverythingFilledIn() As Boolean
'        CREATING FUNCTION TO STEAMLINE CODE TO USE DURING VALIDATION OF CONTROLS ON USERFORM
'        MAKES CODE ESIER TO READ AND MORE FLEXIBLE TO MAKE CHANGES TO
'        LOOP OVER ALL CONTROLS ON THE FORM INSTEAD OF ONE BY ONE
'
'            Dim ctl As MSForms.Control
'            set variable that will look at every single Control on the form be it Text, Date, ComboBox etc use MSForms.Control as this will make certian that we always use contols from MSForms contol - to avoid duplication from other controls
'            Dim txt As MSForms.TextBox
'            variable so thaty code can be made more robust and to make changes so that the intellisense will work
'            Dim AnythingMissing As Boolean
'            to find if anything is missing in textbox to SetFocus to that textbox
'
'            assume everything on userform has been filled in for procedure to work properly
'            EverythingFilledIn = True
'            AnythingMissing = False
'
'            For Each ctl In Me.Controls
'                If TypeOf ctl Is MSForms.TextBox Then
'                    Set txt = ctl 'transfer what is in the ctl variable to txt variable - will make intellisece work as it should
'                    If txt.Value = "" Then 'check if Contols value is an empty string
'                        txt.BackColor = rgbHotPink 'if Contols value is an empty string change Backcolor to Pink
'                        Controls(ctl.Name & "lbl").ForeColor = rgbFireBrick
'                        If Not AnythingMissing Then ctl.SetFocus
'                        AnythingMissing = True
'                        EverythingFilledIn = False
'                    End If
'                End If
'            Next ctl
'
'End Function
 


Reply ↓  Report •
Related Solutions


Ask Question