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

dss28

New Member
Joined
Sep 3, 2020
Messages
45
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
 

dss28

New Member
Joined
Sep 3, 2020
Messages
45
Office Version
  1. 2007
Platform
  1. Windows
Thanks Dave everything fine now.
thank you very much (y)
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

dss28

New Member
Joined
Sep 3, 2020
Messages
45
Office Version
  1. 2007
Platform
  1. Windows
tro
Most welcome, glad able to help & appreciate the feedback

Dave
Troubling you once more.
I used this code in two of my userforms and working fine.
I have another userform where I have 7 text boxes, out of which in 4 text boxes I am recalling data for updation of corresponding 3 text boxes (txb5, txb6,txb7).
Hence in these 3 text boxes, I want the code to run and spare the four text boxes and that they should retain their information.
When I execute the above code, it empties the 4 text boxes (txb1,2,3,4).
thanks in anticipation.
 

dmt32

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

Troubling you once more.
When I execute the above code, it empties the 4 text boxes (txb1,2,3,4).

My solution only checks to ensure control(s) have data entered & reports to user any blank entry - it does not "empty" any control

Suggest post code you have issues with

Dave
 

dss28

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

ADVERTISEMENT

oh. Sorry....
Got the answer,
The 4 text boxes were assigned original grey color and white coloured text matter ..... so as to indicate that the data in these boxes is not to be altered while the other three boxes had white background color and black text font suggesting only to update the data in these three boxes.
When the code was executed, the color in the 4 text boxes changes to white and I thought that the data is deleted as the code turned background color white for all text boxes.

coding is so interesting......
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
oh. Sorry....
Got the answer,
The 4 text boxes were assigned original grey color and white coloured text matter .....

Should be able to adjust function to allow for different background colour

See if this update will do what you want

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
                   If Len(.Tag) = 0 Then .Tag = .BackColor
                    ToBeExcluded = CBool(Not IsError(Application.Match(.Name, arr, 0)))
                    .BackColor = IIf(Len(Trim(ctrl.Value)) = 0 And Not ToBeExcluded, vbRed, Val(.Tag))
                    Missing = Missing + IIf(.BackColor = vbRed, 1, 0)
                End With
            End Select
            ToBeExcluded = False
    Next ctrl
    
    AllComplete = CBool(Missing = 0)
End Function

Dave
 

dss28

New Member
Joined
Sep 3, 2020
Messages
45
Office Version
  1. 2007
Platform
  1. Windows
thanks Dave, its so fantastic to take guidance from you, and you had quick answers to all my doubts.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
thanks Dave, its so fantastic to take guidance from you, and you had quick answers to all my doubts.

There are others on this forum that are normally much quicker than me at responding - pleased suggestions resolve your issue

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,122,255
Messages
5,595,106
Members
413,970
Latest member
Hodg100

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top