exceltextboxvba

Check if all textboxes in a group are filled


I would like to check 2 groups of textboxes on a multipage userform (4 with tag "A" and 4 with tag "B") in realtime.

If one group is filled (all textboxes in the group contain a value) a button should be enabled.

Main Code:

Dim TextBoxes() As New ChangeCheck
Private Sub UserForm_Initialize()
    Main.ZeitA_CommandButton1.Enabled = False
    Dim Ctl As MSForms.Control
    Dim i As Long
    i = 1
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "TextBox" Then
            ReDim Preserve TextBoxes(1 To i)
            Set TextBoxes(i).TextGroup = Ctl
            i = i + 1
        End If
    Next Ctl
End Sub

Class Module "ChangeCheck"

Option Explicit
Public WithEvents TextGroup As MSForms.Textbox
    
Sub TextGroup_Change()
    If TextGroup.Tag = "A" Then
        MsgBox "Hallo"
    End If
End Sub

If I change a textbox with the tag "A" I get a MsgBox.

If I use

If TextGroup.Tag = "A" And TextGroup.Value <> "" Then

each single textbox will activate or deactivate the button.

How can I check if all with the tag "A" are filled?


Solution

  • There is one of possible solutions below.

    It is assumed that there is form named UserForm1 containing 8 TextBox controls (4 with tag "A" and 4 with tag "B" as you mentioned) and 2 CommandButton controls named CommandButton1 for group "A" and CommandButton2 for "B". Place this code into UserForm1 section (some variable names were replaces with more instructive IMO, also I used collection instead of array so the counter variable is not needed):

    Option Explicit
    
    Private Sub UserForm_Initialize()
        Dim Ctl As MSForms.Control
        Dim ChangeCheckInstance As ChangeCheck
        Set Form = Me
        Me.CommandButton1.Enabled = False
        Me.CommandButton2.Enabled = False
        For Each Ctl In Me.Controls
            If TypeName(Ctl) = "TextBox" Then
                Set ChangeCheckInstance = New ChangeCheck
                Set ChangeCheckInstance.TextBoxCtl = Ctl
                ChangeChecks.Add ChangeCheckInstance
            End If
        Next Ctl
    End Sub
    

    Also projects have to have Module1 in modules section with code as follows:

    Option Explicit
    
    Public ChangeChecks As New Collection
    Public Form As Object
    
    Public Sub CheckTextBoxes(CallerTextBox As MSForms.TextBox)
        Dim ChangeCheckInstance As ChangeCheck
        Dim NotEmpty As Boolean
        NotEmpty = True
        For Each ChangeCheckInstance In ChangeChecks
            With ChangeCheckInstance.TextBoxCtl
                If (.Text = "") And (.Tag = CallerTextBox.Tag) Then
                    NotEmpty = False
                    Exit For
                End If
            End With
        Next
        Select Case CallerTextBox.Tag
        Case "A"
            Form.CommandButton1.Enabled = NotEmpty
        Case "B"
            Form.CommandButton2.Enabled = NotEmpty
        End Select
    End Sub
    

    And your ChangeCheck class definition in class modules section:

    Option Explicit
    
    Public WithEvents TextBoxCtl As MSForms.TextBox
    
    Sub TextBoxCtl_Change()
        CheckTextBoxes TextBoxCtl
    End Sub
    

    As you can see all check operations are implemented in Public Sub CheckTextBoxes, that takes necessary data from argument CallerTextBox passed from changed class instance, and global variables: ChangeChecks collection with all checkboxes, Form that refers to the UserForm1.

    EDIT:

    If you don't want to have global variables (it's not the best practice as you know), another way is to make encapsulation of check method and necessary data within class. So you don't need to keep Module1 in modules section, UserForm1 code in forms section will be as follows:

    Option Explicit
    
    Private Sub UserForm_Initialize()
        Dim Ctl As MSForms.Control
        Dim ChangeChecks As New Collection
        Dim ChangeCheckInstance As ChangeCheck
        Me.CommandButton1.Enabled = False
        Me.CommandButton2.Enabled = False
        For Each Ctl In Me.Controls
            If TypeName(Ctl) = "TextBox" Then
                Set ChangeCheckInstance = New ChangeCheck
                Set ChangeCheckInstance.TextBoxCtl = Ctl
                Set ChangeCheckInstance.ChangeChecks = ChangeChecks
                Set ChangeCheckInstance.Form = Me
                ChangeChecks.Add ChangeCheckInstance
            End If
        Next Ctl
    End Sub
    

    and your ChangeCheck class definition in class modules section will be as follows:

    Option Explicit
    
    Public WithEvents TextBoxCtl As MSForms.TextBox
    Public ChangeChecks As Collection
    Public Form As Object
    
    Sub TextBoxCtl_Change()
        Dim ChangeCheckInstance As ChangeCheck
        Dim NotEmpty As Boolean
        NotEmpty = True
        For Each ChangeCheckInstance In ChangeChecks
            With ChangeCheckInstance.TextBoxCtl
                If (.Text = "") And (.Tag = TextBoxCtl.Tag) Then
                    NotEmpty = False
                    Exit For
                End If
            End With
        Next
        Select Case TextBoxCtl.Tag
        Case "A"
            Form.CommandButton1.Enabled = NotEmpty
        Case "B"
            Form.CommandButton2.Enabled = NotEmpty
        End Select
    End Sub