Excel VBA动态创建文本框后无法读取值的问题求助

Hey there! I’ve dealt with this exact problem before—dynamic controls in VBA can be tricky because of how their scope works. Let’s break down why you can’t read the quarter1 textbox value and how to fix it.

Common Reasons for the Problem #

• Scope Issues: If you only create the textbox inside the buttonAdd_Click event without storing a reference to it, the control object goes out of scope once the event finishes. That means you can’t access it later using a direct name like Me.quarter1.
• Incorrect Access Method: Since the textbox is created at runtime (not in design mode), VBA doesn’t recognize Me.quarter1 as a valid control by default. You need to use the Controls collection or a stored reference to access it.

Step-by-Step Solution #

1. Declare a Module-Level Collection to Store TextBox References #

At the top of your UserForm’s code module (outside any subroutine), declare a collection to keep track of all dynamically created textboxes. This ensures the references stay in scope even after the button click event ends:

Private dynamicTextBoxes As New Collection

2. Update the buttonAdd_Click Event to Save References #

Modify your button click code to add each new textbox to the collection, along with setting its properties:

Private Sub buttonAdd_Click()
    Dim newTextBox As MSForms.TextBox
    Dim textBoxName As String
    Dim a As Integer
    
    ' Replace this with your actual input value for variable "a"
    a = 1 
    textBoxName = "quarter" & a
    
    ' Create the new textbox
    Set newTextBox = Me.Controls.Add("Forms.TextBox.1", textBoxName, True)
    
    ' Set position/size (adjust these values to fit your UserForm)
    newTextBox.Top = 10 + (a - 1) * 30 ' Stack textboxes vertically
    newTextBox.Left = 20
    newTextBox.Width = 100
    newTextBox.Height = 20
    
    ' Add the textbox to our module-level collection
    dynamicTextBoxes.Add newTextBox, textBoxName
End Sub

3. Read the TextBox Value Correctly #

Now you can access the textbox’s value in two reliable ways:

Method 1: Use the UserForm’s Controls Collection

Check if the control exists first to avoid errors, then read its value:

Private Sub btnSaveValue_Click()
    Dim textBoxValue As String
    
    ' Verify the textbox exists before accessing it
    If Me.Controls.Exists("quarter1") Then
        textBoxValue = Me.Controls("quarter1").Text
        ThisWorkbook.Sheets("Sheet1").Range("A1").Value = textBoxValue
    Else
        MsgBox "The quarter1 textbox doesn't exist yet!"
    End If
End Sub

Method 2: Use the Module-Level Collection (More Reliable)

Since we stored references in the dynamicTextBoxes collection, we can retrieve the textbox directly:

Private Sub btnSaveValue_Click()
    Dim targetTextBox As MSForms.TextBox
    
    ' Use error handling to check if the textbox is in the collection
    On Error Resume Next
    Set targetTextBox = dynamicTextBoxes("quarter1")
    On Error GoTo 0
    
    If Not targetTextBox Is Nothing Then
        ThisWorkbook.Sheets("Sheet1").Range("A1").Value = targetTextBox.Text
    Else
        MsgBox "The quarter1 textbox doesn't exist yet!"
    End If
End Sub

Key Takeaways #

• Always store references to dynamically created controls in a module-level variable (like a collection or array) to keep them in scope.
• Never use Me.ControlName for runtime-created controls—use Me.Controls("ControlName") or your stored reference instead.
• Add error checking to handle cases where the textbox hasn’t been created yet.

内容的提问来源于stack exchange，提问作者Jaka E Sembodo