vba control/check comboboxes/textboxes are empty or not

[DB73]

Ive made a form with several comboboxes en textboxes
if i populate these i let a "command button"write the text/value to my dumpstats sheet.
but sometimes i forget to populate 1 of these boxes.

is it possible to add some vba to that command button, before it sends all value to the dumpstats sheet, too check if 1 or more of the boxes are empty and then color the empty red so i can fill them in.
after completion to let it write to my dumpstats sheet.

at first i made it just with a message boxes, thats shows "everyting filled in correctly ?", but that isnt actulay checking and sometime is just say yes , and then i get some faults.

thanks in advance

 

[Anthony47]

Try this snippet, to be inserted within the CommandButton_Click macro:

ckArray = Array("ComboBox1", "TextBox1", "Textbox2")         '<<< Here the full list of the controls
For i = 0 To UBound(ckArray)
    If Len(Me.Controls(ckArray(i))) = 0 Then
        Me.Controls(ckArray(i)).BackColor = RGB(255, 150, 150)   '??? Red Color
        Me.Controls(ckArray(i)).SetFocus
        Exit Sub
    End If
Next i

This works for textboxes and comboboxes
If the listed controls don't have a value the code set the focus to the empty control and set its background color to red. You have to remove this color using the Change Event of each of the involved controls

Try...

 

[DB73]

thanks for the repley.

i found someting similar by my self, but i did it for each combobox separtly.
yours is just a lot smaller....thanks for that
as i understand, i have to make for each cb an change event to turn the color back from red to my "own color" when an item is selected after its turns into red in the cb

right ?

 

[dmt32]

Hi,
if you want to ensure ALL textboxes / comboboxes are completed on your form then give this a try

Place in a STANDARD module

Function IsComplete(ByVal Form As Object) As Boolean
    Dim ctrl        As Control
    Dim lngCount    As Long
    
    For Each ctrl In Form.Controls
        Select Case TypeName(ctrl)
            Case "TextBox", "ComboBox"
                If Len(ctrl.Value) = 0 Then
                    ctrl.BackColor = vbRed
                    If lngCount = 0 Then ctrl.SetFocus
                    lngCount = lngCount + 1
                Else
                    ctrl.BackColor = vbWhite
                End If
        End Select
    Next ctrl
    
    If lngCount > 0 Then
        MsgBox "Please Complete " & lngCount & " Field(s) Shown In Red", 48, "Entry Required"
    Else
        IsComplete = True
    End If
    
End Function

and to call from your userform

Private Sub CommandButton1_Click()

    If Not IsComplete(Me) Then Exit Sub
    
    'rest of code
End Sub

Function will highlight ALL controls that need completion & can be used for other userforms in your project if needed

Dave

 

[Anthony47]

Your understanding is correct. So, for example (with the three controls I used in my example):

Private Sub TextBox1_Change()
With Me.TextBox1
    If Len(.Value) > 0 Then
        .BackColor = RGB(255, 255, 255)   'or Your Color
    Else
        .BackColor = RGB(255, 150, 150)
    End If
End With
End Sub

Private Sub TextBox2_Change()
With Me.TextBox2
    If Len(.Value) > 0 Then
        .BackColor = RGB(255, 255, 255)   'or Your Color
    Else
        .BackColor = RGB(255, 150, 150)
    End If
End With
End Sub

Private Sub ComboBox1_Change()
Beep
With Me.ComboBox1
    If .ListIndex >= 0 Then
        .BackColor = RGB(255, 255, 255)   'or Your Color
    Else
        .BackColor = RGB(255, 150, 150)
    End If
End With
End Sub

As you can see, I have "anticipated" at the change event flagging for missing data

This is in addition to what suggested by Dave, above

 

[DB73]

thanks Dave...
i prefer what Anthony47 did, because sometimes a cb depends on another cb and then it locks and changes color, otherwise i have to change all the other codes...at least that's what i think, i have created a message box for each combox so I know which cb still needs to be filled in...thanks anyway...I learned something again...still learning vba

 

[dmt32]

thanks Dave...
i prefer what Anthony47 did, because sometimes a cb depends on another cb and then it locks and changes color, otherwise i have to change all the other codes...at least that's what i think

Solution you use is your choice and appreciate your feedback but to clarify, with mine you don't need any change events for each of the controls - function tests ALL the controls in one go & then returns True when all complete.

You only need this line of code in your commandbutton code

If Not IsComplete(Me) Then Exit Sub

Dave

 

[DB73]

Solution you use is your choice and appreciate your feedback but to clarify, with mine you don't need any change events for each of the controls - function tests ALL the controls in one go & then returns True when all complete.

You only need this line of code in your commandbutton code

If Not IsComplete(Me) Then Exit Sub

Dave

Dave,

im bussy with yours...
what i found is that as i didnt fill in 1 of the cb its turns into red, thats what ik want....but when i sellect an item in that cb, al other cb turn into green(thats my color,) but the one i forgot to fill in stays red

cb1 is "werkdag/verlof/ziek", wich is 1 of the cb i forgot to fill in (as example) "image 1"
then i fill in cb2 "project", this one and the rest is already turned into light green back again, but cb1 is still red.

my button "voeg toe" is cmbtn5
if every cb is filled in then the data goes to my dumpstats sheet

 

[Anthony47]

@dmt32

with mine you don't need any change events for each of the controls

Change events are needed only if we wish to remove the red as soon as we start recovering the error

 

[dmt32]

@dmt32

Change events are needed only if we wish to remove the red as soon as we start recovering the error

Thanks and I do appreciate that - my suggestion did not consider that as a requirement.

Dave