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?
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