基于用户选择的Userform VBA设计修改：新增多项目ID输入功能

Got it, let's build that dynamic project ID input feature for your VBA user form. I'll walk you through each step to integrate this smoothly with your existing setup:

First, open your user form in design mode and add these elements:

• A Frame control (name it ProjectIDs_Frame), set its Visible property to False initially. This will hold all the project ID input fields.
• Inside the frame, add one TextBox (name it ProjectID_TextBox1) – this is your starting input field.
• Add two CommandButton controls next to the frame (or inside it, for better layout):
  • Name one AddID_Button, set its Caption to +
  • Name the other RemoveID_Button, set its Caption to -, and set its Enabled property to False initially (since you can't remove the only input field)

Open the form's code module and modify the ProjectIDProgram_ComboBox_Change event to show/hide the dynamic input area based on the user's selection:

Private Sub ProjectIDProgram_ComboBox_Change()
    ' Show/hide the project ID frame based on selection
    If ProjectIDProgram_ComboBox.Value = "Program" Then
        ProjectIDs_Frame.Visible = True
        ' Reset to one input field if switching back to Program
        ClearExtraProjectIDFields
    Else
        ProjectIDs_Frame.Visible = False
        ' Clear any existing project ID fields when switching to Project
        ClearExtraProjectIDFields
    End If
    ' Re-check if all fields are filled to update Ok button state
    UpdateOkButtonState
End Sub

Private Sub ClearExtraProjectIDFields()
    ' Remove all extra text boxes except the first one
    Dim ctrl As Control
    For Each ctrl In ProjectIDs_Frame.Controls
        If TypeName(ctrl) = "TextBox" And ctrl.Name <> "ProjectID_TextBox1" Then
            ProjectIDs_Frame.Controls.Remove ctrl.Name
        End If
    Next ctrl
    ' Reset the remove button state
    RemoveID_Button.Enabled = False
    ' Clear the first text box
    ProjectID_TextBox1.Value = ""
End Sub

Add these click event handlers to handle dynamic field creation and removal:

Private Sub AddID_Button_Click()
    Dim newTextBox As MSForms.TextBox
    Dim lastTextBox As MSForms.TextBox
    Dim nextIndex As Integer
    
    ' Find the index of the last existing text box
    nextIndex = 1
    For Each ctrl In ProjectIDs_Frame.Controls
        If TypeName(ctrl) = "TextBox" Then
            nextIndex = nextIndex + 1
        End If
    Next ctrl
    
    ' Create new text box
    Set newTextBox = ProjectIDs_Frame.Controls.Add("Forms.TextBox.1", "ProjectID_TextBox" & nextIndex, True)
    
    ' Position the new text box below the last one (adjust top/left as needed for your layout)
    Set lastTextBox = ProjectIDs_Frame.Controls("ProjectID_TextBox" & (nextIndex - 1))
    newTextBox.Top = lastTextBox.Top + lastTextBox.Height + 5 ' 5px spacing between fields
    newTextBox.Left = lastTextBox.Left
    newTextBox.Width = lastTextBox.Width
    newTextBox.Height = lastTextBox.Height
    
    ' Enable the remove button since we now have multiple fields
    RemoveID_Button.Enabled = True
    
    ' Re-check Ok button state
    UpdateOkButtonState
End Sub

Private Sub RemoveID_Button_Click()
    Dim ctrl As Control
    Dim lastTextBoxIndex As Integer
    Dim lastTextBoxName As String
    
    ' Find the highest index of existing text boxes
    lastTextBoxIndex = 1
    For Each ctrl In ProjectIDs_Frame.Controls
        If TypeName(ctrl) = "TextBox" Then
            Dim currentIndex As Integer
            currentIndex = CInt(Right(ctrl.Name, Len(ctrl.Name) - Len("ProjectID_TextBox")))
            If currentIndex > lastTextBoxIndex Then
                lastTextBoxIndex = currentIndex
            End If
        End If
    Next ctrl
    
    ' Only remove if there's more than 1 text box
    If lastTextBoxIndex > 1 Then
        lastTextBoxName = "ProjectID_TextBox" & lastTextBoxIndex
        ProjectIDs_Frame.Controls.Remove lastTextBoxName
    End If
    
    ' Disable remove button if only one field remains
    Dim textBoxCount As Integer
    textBoxCount = 0
    For Each ctrl In ProjectIDs_Frame.Controls
        If TypeName(ctrl) = "TextBox" Then textBoxCount = textBoxCount + 1
    Next ctrl
    RemoveID_Button.Enabled = textBoxCount > 1
    
    ' Re-check Ok button state
    UpdateOkButtonState
End Sub

Modify your existing field-checking function to include the dynamic project ID fields when "Program" is selected. Here's an example of what that could look like:

Private Sub UpdateOkButtonState()
    Dim allFilled As Boolean
    Dim ctrl As Control
    
    ' Start with assuming all fields are filled
    allFilled = True
    
    ' Check your original text/combo boxes (replace these with your actual control names)
    If YourNameTextBox.Value = "" Or YourStatusComboBox.Value = "" Then
        allFilled = False
    End If
    
    ' If Program is selected, check all dynamic project ID fields
    If ProjectIDProgram_ComboBox.Value = "Program" Then
        For Each ctrl In ProjectIDs_Frame.Controls
            If TypeName(ctrl) = "TextBox" And ctrl.Value = "" Then
                allFilled = False
                Exit For
            End If
        Next ctrl
    End If
    
    ' Enable/disable Ok button based on result
    Ok_Button.Enabled = allFilled
End Sub

Don't forget to call UpdateOkButtonState in the Change or AfterUpdate events of all your original controls too, so the Ok button updates as users fill out fields.

• Adjust the positioning values (top/left/spacing) in the AddID_Button_Click sub to match your form's layout.
• If your frame has other controls besides text boxes, the code already accounts for that by filtering for only text box controls.
• Test edge cases: make sure switching between Project/Program resets the fields correctly, and you can't remove the last project ID input.

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