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.

score 1 · Answer 1 · answered Dec 15 '18 at 05:54

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

score 0 · Answer 2 · answered Dec 15 '18 at 03:39

The easier way is to validate on click: in CommandButton2_Click, scan your dynamically created textboxes, and either proceed or notify the user about any validation error.

A more complicated way is to create a class that will monitor the events of a TextBox. You will create one instance of this class per TextBox you want to monitor, keeping those instances in e.g. an array. See How to add events to Controls created at runtime in Excel with VBA.

K.Dᴀᴠɪs · Answer 3 · 2018-12-15T04:28:10.007

You can loop through each worksheet in your workbook, and for each worksheet - loop through all the OLEObjects. You will check the typename of the .Object, and perform your final tests there.

I would create a function that you can easily call to perform this check and return a Boolean True/False.

Function allTextboxEmpty() As Boolean

    Dim oleObj As OLEObject, ws As Worksheet
    allTextboxEmpty = True

    For Each ws In ThisWorkbook.Worksheets
        For Each oleObj In ws.OLEObjects
            If TypeName(oleObj.Object) = "TextBox" Then
                If oleObj.Object.Value <> vbNullString Then
                    allTextboxEmpty = False
                    Exit Function
                End If
            End If
        Next oleObj
    Next ws

End Function

If the above function returns True, then you know that all of your textboxes in the workbook are empty. You can use this function as shown in the below example:

If allTextboxEmpty Then
    Worksheets("Sheet1").CommandButton2.Enabled = False
Else
    Worksheets("Sheet1").CommandButton2.Enabled = True
End If

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

3 Answers3

Linked