CODEWITHSUNDEEP
exceltextboxvba
Moritz
Moritz

Reputation: 389

Excel VBA - Check group of textboxes

I am facing a little problem with my current project at work.

I would like to check 2 groups of textboxes on a multipage userform (4 with tag "A" and 4 with tag "B") in realtime and if one group of them is not empty (each single textbox has to contain a value) a button should be enabled.

This is what I have so far:

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

So if I change a textbox with the tag "A" I get a MsgBox but how can I check if all with the tag "A" are not empty? If I use 

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

each single textbox will activate or deactivate the button

Thank you in advance Moritz

Upvotes: 0

Views: 4338

Answers (1)

omegastripes
omegastripes

Reputation: 12612

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

Upvotes: 1

Related Questions