VBA code for Validation of data entry by passing / not working

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
I am very new to VBA coding / excel as such. However I have designed a VBA based program for my needs based on several online VDOs and working satisfactorily. However the validation code for data entry is not working at all and data can be processed to next step without entering full information as required in the user form. i have included a part of the validation code. please help me in resolving the issue.
VBA Code:
Function ValidEntry() As Boolean

    ValidEntry = True
    
    With frmADLPOForm
    
        'Default Color
        
              .txbDescription.BackColor = vbWhite    '8

             If Trim(.txbDescription.Value) = "" Then
        
            MsgBox "Please enter Description", vbOKOnly + vbInformation, "Description"
            .txbDescription.BackColor = vbRed
            .txbDescription.SetFocus
            
            ValidEntry = False
            Exit Function
        
        End If
        
    End With

End Function
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi.
Your first line you set valid entry flag to true then you set it false only in the user enters nothing. If the user were to cancel out the function can still exit with true. Best to set the flag to false to start 5he set it true if the entry is valid.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,283
Office Version
  1. 2019
Platform
  1. Windows
Hi,
your function checks one specific textbox control - by the "next step" do you test other textboxes or different controls?
It would be useful if you can share the code that calls the function

Dave
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
thanks for rep
Hi,
your function checks one specific textbox control - by the "next step" do you test other textboxes or different controls?
It would be useful if you can share the code that calls the function

Dave
Thanks for reply.
There are other three text boxes with similar code like Sr.No., Name, Address, hence not given in the above code. However the code starts and ends as stated above.
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi.
Your first line you set valid entry flag to true then you set it false only in the user enters nothing. If the user were to cancel out the function can still exit with true. Best to set the flag to false to start 5he set it true if the entry is valid.
Thanks for reply.
I have no basic knowledge on the coding as the code was copied from the VDOs / tutorials from internet. It will be very nice if full corrected code can be posted for help.
In the mean time i will try to replace the true / false words and see the working of code so that I can learn and review
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
Hi.
Your first line you set valid entry flag to true then you set it false only in the user enters nothing. If the user were to cancel out the function can still exit with true. Best to set the flag to false to start 5he set it true if the entry is valid.
I replaced the True with False and False with true in the code but still no message pops up or text box color does not change and proceeds with saving of data without any data entered into the text box.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,283
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

thanks for rep

There are other three text boxes with similar code like Sr.No., Name, Address, hence not given in the above code. However the code starts and ends as stated above.

You should only need one function perform the test for each textbox

Try following update to your code & see if helps

Function Code for ALL textboxes
VBA Code:
Function ValidEntry(ByVal TextBox As Object, ByVal Prompt As String) As Boolean
    ValidEntry = Len(Trim(TextBox.Value)) > 0
    TextBox.BackColor = IIf(ValidEntry, vbWhite, vbRed)
    If Not ValidEntry Then MsgBox "Please enter " & Prompt, 64, Prompt
End Function

Call from each textbox exit event as required

VBA Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not ValidEntry(Me.TextBox1, "Description")
End Sub

The exit event has the cancel parameter which stops exit until textbox completed - However, users may find this a little annoying & it may be better to test that all required textboxes have been completed when users presses commandbutton

something like

Your entry code commandbtton
VBA Code:
Private Sub CommandButton1_Click()
    Dim CountMissing As Integer
    If Not AllComplete(Me, CountMissing) Then
        MsgBox "Please complete the " & CountMissing & " textbox(es) shown in red", 16, "Entry Required"
        Exit Sub
    End If
   
    'rest of code
   
End Sub

Function Code

VBA Code:
Function AllComplete(ByVal Form As Object, ByRef Missing As Integer) As Boolean
    Dim txtbox As Control
    For Each txtbox In Form.Controls
            If TypeOf txtbox Is MSForms.TextBox Then
                With txtbox
                    .BackColor = IIf(Len(Trim(txtbox.Value)) = 0, vbRed, vbWhite)
                    Missing = Missing + IIf(.BackColor = vbRed, 1, 0)
                End With
            End If
    Next txtbox
    AllComplete = CBool(Missing = 0)
End Function

When user presses button, function checks ALL textboxes have been completed & if not, sets background red. Msgbox Displays how many textboxes need to be completed before use r can procesed - Function can be modified if not all textboxes are required to be tested.

Dave
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
You should only need one function perform the test for each textbox

Try following update to your code & see if helps

Function Code for ALL textboxes
VBA Code:
Function ValidEntry(ByVal TextBox As Object, ByVal Prompt As String) As Boolean
    ValidEntry = Len(Trim(TextBox.Value)) > 0
    TextBox.BackColor = IIf(ValidEntry, vbWhite, vbRed)
    If Not ValidEntry Then MsgBox "Please enter " & Prompt, 64, Prompt
End Function

Call from each textbox exit event as required

VBA Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not ValidEntry(Me.TextBox1, "Description")
End Sub

The exit event has the cancel parameter which stops exit until textbox completed - However, users may find this a little annoying & it may be better to test that all required textboxes have been completed when users presses commandbutton

something like

Your entry code commandbtton
VBA Code:
Private Sub CommandButton1_Click()
    Dim CountMissing As Integer
    If Not AllComplete(Me, CountMissing) Then
        MsgBox "Please complete the " & CountMissing & " textbox(es) shown in red", 16, "Entry Required"
        Exit Sub
    End If
  
    'rest of code
  
End Sub

Function Code

VBA Code:
Function AllComplete(ByVal Form As Object, ByRef Missing As Integer) As Boolean
    Dim txtbox As Control
    For Each txtbox In Form.Controls
            If TypeOf txtbox Is MSForms.TextBox Then
                With txtbox
                    .BackColor = IIf(Len(Trim(txtbox.Value)) = 0, vbRed, vbWhite)
                    Missing = Missing + IIf(.BackColor = vbRed, 1, 0)
                End With
            End If
    Next txtbox
    AllComplete = CBool(Missing = 0)
End Function

When user presses button, function checks ALL textboxes have been completed & if not, sets background red. Msgbox Displays how many textboxes need to be completed before use r can procesed - Function can be modified if not all textboxes are required to be tested.

Dave

Good Morning,
Thanks Dave, this is working absolutely fine. I have used the last two codes to check all the textboxes at one go for filled data entry. Can you please further guide on:

1) Can I modify the code to include combobox also in future in similar way ?
2) how can I omit few textboxes from my checkpoint,
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
in addition to above for my knowledge purpose, the code I used initially is working fine in the demo excel file downloaded from internet on which i built up my code and userform. So what could have gone wrong is still beyond my reach.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,283
Office Version
  1. 2019
Platform
  1. Windows
1) Can I modify the code to include combobox also in future in similar way ?
2) how can I omit few textboxes from my checkpoint,

Following update to Function should meet both requirements

VBA Code:
Function AllComplete(ByVal Form As Object, ByRef Missing As Integer, ParamArray ExcludedControls()) As Boolean
    Dim ctrl As Control
    Dim arr As Variant
    Dim ToBeExcluded As Boolean
    
    If Not IsMissing(ExcludedControls) Then arr = ExcludedControls
    
    For Each ctrl In Form.Controls
            Select Case True
            Case TypeOf ctrl Is MSForms.TextBox, TypeOf ctrl Is MSForms.ComboBox
                With ctrl
                    ToBeExcluded = CBool(Not IsError(Application.Match(.Name, arr, 0)))
                    .BackColor = IIf(Len(Trim(ctrl.Value)) = 0 And Not ToBeExcluded, vbRed, vbWhite)
                    Missing = Missing + IIf(.BackColor = vbRed, 1, 0)
                End With
            End Select
            ToBeExcluded = False
    Next ctrl
    
    AllComplete = CBool(Missing = 0)
End Function

The function should be placed in a STANDARD module if you have other userforms in your project that also required testing of control values.

To call function & exclude certain controls

Rich (BB code):
Private Sub CommandButton1_Click()
    Dim CountMissing As Integer
    If Not AllComplete(Me, CountMissing, "TextBox1", "TextBox4") Then
        MsgBox "Please complete the " & CountMissing & " Controls(s) shown in red", 16, "Entry Required"
        Exit Sub
    End If
End Sub

Add all the control names as required shown in BOLD above that you want to exclude from the test - If you omit this argument, all controls will be tested.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,118,886
Messages
5,574,834
Members
412,620
Latest member
sharma7s
Top