如何设置UserForm提交按钮需选中工作表且填完所有TextBox才可启用？

To achieve your goal—enabling the submit button only when both a worksheet is selected in ListBox AND all TextBoxes are filled—we’ll use a reusable helper function to check both conditions, then trigger this check whenever relevant controls change.

Step 1: Create a Helper Function to Check Conditions #

This function will:

• Verify all TextBoxes on the form have non-empty values (ignoring whitespace with Trim)
• Check if a worksheet is selected in the ListBox
• Update the button’s enabled status based on both checks

Private Sub UpdateButtonStatus()
    Dim ctrl As Control
    Dim allTextBoxesFilled As Boolean
    allTextBoxesFilled = True
    
    ' Check every TextBox for non-empty content
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            If Trim(ctrl.Value) = "" Then
                allTextBoxesFilled = False
                Exit For ' Stop checking once an empty TextBox is found
            End If
        End If
    Next ctrl
    
    ' Enable button ONLY if both conditions are met
    CommandButton1.Enabled = (Me.ListBox1.ListIndex <> -1) And allTextBoxesFilled
End Sub

Step 2: Update UserForm Initialization #

Ensure the button starts disabled, populate your ListBox, and run the initial status check:

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    CommandButton1.Enabled = False
    
    ' Populate ListBox with worksheet names (your existing code)
    For Each ws In ThisWorkbook.Worksheets
        Me.ListBox1.AddItem ws.Name
    Next ws
    
    ' Run initial check to set button state
    UpdateButtonStatus
End Sub

Step 3: Trigger Checks When Controls Change #

We need to call our helper function whenever the user interacts with the ListBox or any TextBox:

For the ListBox:

Private Sub ListBox1_Change()
    UpdateButtonStatus
End Sub

For Each TextBox:

If you have a small number of TextBoxes, add this event handler for each one (replace TextBox1 with your actual TextBox names):

Private Sub TextBox1_Change()
    UpdateButtonStatus
End Sub

Private Sub TextBox2_Change()
    UpdateButtonStatus
End Sub

' Repeat for all TextBoxes on your form

Alternative: Handle All TextBoxes with a Class Module (For Many TextBoxes) #

If your form has lots of TextBoxes, writing individual event handlers is tedious. Use a class module to handle all TextBox Change events in one place:

1. Insert a new class module (Right-click your project in VBA Editor > Insert > Class Module)
2. Name it clsTextBoxHandler (in the Properties window)
3. Add this code to the class module:

   Public WithEvents TextBox As MSForms.TextBox
   
   Private Sub TextBox_Change()
       ' Call the helper function in your UserForm
       UserForm1.UpdateButtonStatus ' Replace UserForm1 with your form's name
   End Sub
   

4. Add this code to your UserForm module to initialize the class handlers:

   Dim txtHandlers As Collection
   
   Private Sub UserForm_Initialize()
       Dim ws As Worksheet
       Dim ctrl As Control
       Dim txtHandler As clsTextBoxHandler
       
       Set txtHandlers = New Collection
       CommandButton1.Enabled = False
       
       ' Populate ListBox (your existing code)
       For Each ws In ThisWorkbook.Worksheets
           Me.ListBox1.AddItem ws.Name
       Next ws
       
       ' Assign handlers to all TextBoxes
       For Each ctrl In Me.Controls
           If TypeName(ctrl) = "TextBox" Then
               Set txtHandler = New clsTextBoxHandler
               Set txtHandler.TextBox = ctrl
               txtHandlers.Add txtHandler
           End If
       Next ctrl
       
       UpdateButtonStatus
   End Sub
   

Key Notes: #

• The Trim function ensures that users can’t just enter spaces to bypass validation. If you want to allow whitespace, remove Trim.
• ListBox1.ListIndex <> -1 checks if any item is selected (since -1 means no selection).
• The helper function centralizes the logic, making it easy to modify later (e.g., add exceptions for specific TextBoxes if needed).

内容的提问来源于stack exchange，提问作者sway