excel - about multipage: Unless all pages have input, commandbuttom2 is disabled

Question

I have a code here that will generate pages depends on what value is on the textbox.

'Button accepting how many number of pages
Private Sub CommandButton1_Click()

RowChar = 70
MultiPage1.Pages.Clear

For i = 0 To TextBox1.Value - 1
    MultiPage1.Pages.Add
    MultiPage1.Pages(i).Caption = "Variable" & i + 1

    Call LabelPerPage

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "NameBox")
    With txtbx
        .Top = 20
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MinBox")
    With txtbx
        .Top = 50
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "LsbBox")
    With txtbx
        .Top = 20
        .Left = 300
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "Mataas")
    With txtbx
        .Top = 50
        .Left = 300
    End With

    If i = 0 Then
        FormulaString = "= C15"
    Else
        FormulaString = FormulaString & "  " & Chr(RowChar) & "15"
        RowChar = RowChar + 3
    End If

Next i
TextBox2.Value = FormulaString
End Sub

Problem: I want to disable commandbutton2(button for computation of MINbox and MAxbox) if all the textboxes inside each pages are empty. Do you have any IDEA how can I do that? Thank you.

Answer 1

Though best way and easiest way is to validate on click in CommandButton2_Click as answered by @Excelosaurus, i just offering slightly modified way of TextBox change event trapping by @Mathieu Guindon's answer in the post Implementing a change event to check for changes to textbox values and enabling the “apply” button. The full credit of this technique of encapsulating a WithEvents MSForms control goes to @Mathieu Guindon

in the Userform1 code module may be modified as below

Public handlers As VBA.Collection     ' added
Private Sub CommandButton1_Click()
RowChar = 70
MultiPage1.Pages.Clear

For i = 0 To TextBox1.Value - 1
    MultiPage1.Pages.Add
    MultiPage1.Pages(i).Caption = "Variable" & i + 1

    'Call LabelPerPage

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "NameBox")
    With txtbx
        .Top = 20
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MinBox")
    With txtbx
        .Top = 50
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "LsbBox")
    With txtbx
        .Top = 20
        .Left = 300
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "Mataas")
    With txtbx
        .Top = 50
        .Left = 300
    End With

    If i = 0 Then
        FormulaString = "= C15"
    Else
        FormulaString = FormulaString & "  " & Chr(RowChar) & "15"
        RowChar = RowChar + 3
    End If

Next i
TextBox2.Value = FormulaString
CommandButton2.Enabled = False ' added
makeEvents                     ' added   
End Sub

Sub makeEvents()                 ' added             
    Set handlers = New VBA.Collection
    Dim cnt As MSForms.Control

    For i = 0 To UserForm1.MultiPage1.Pages.Count - 1
    For Each cnt In UserForm1.MultiPage1.Pages(i).Controls
        If TypeOf cnt Is MSForms.TextBox Then
            Dim textBoxHandler As DynamicTextBox
            Set textBoxHandler = New DynamicTextBox
            textBoxHandler.Initialize cnt
            handlers.Add textBoxHandler
        'Debug.Print cnt.Name & i & "Inited"
        End If
     Next cnt
     Next i
End Sub

Then Add a new class module to your project, call it DynamicTextBox

Option Explicit
Private WithEvents encapsulated As MSForms.TextBox
Public Sub Initialize(ByVal ctrl As MSForms.TextBox)
    Set encapsulated = ctrl
End Sub
Private Sub encapsulated_Change()
Dim TextEmpty As Boolean
Dim cnt As Control
Dim i As Integer

For i = 0 To UserForm1.MultiPage1.Pages.Count - 1
    For Each cnt In UserForm1.MultiPage1.Pages(i).Controls
        If TypeOf cnt Is MSForms.TextBox Then
        'Debug.Print cnt.Name & i & "checked"
            If cnt.Value = "" Then
            TextEmpty = True
            Exit For
            End If
        End If
    Next cnt
    If TextEmpty = True Then
    Exit For
    End If
Next i


If TextEmpty Then
UserForm1.CommandButton2.Enabled = False
Else
UserForm1.CommandButton2.Enabled = True
End If

End Sub

Tried and found working