Excel VBA用户表单-使用相同的表单生成连续数据

这是一个快速的POC。考虑一个带有文本框和两个命令按钮的纯简用户格式。我根本没有更改默认名称。刚刚添加了一个带有textbox1，commandbutton1和commandbutton2的用户窗体：

CommandButton1提交并CommandButton2退出用户表单（例如，用户完成提交后单击）。

使代码按您描述的那样工作的代码。这在用户表单的代码页中。

'declare a string array to hold the submissions from textbox1
Private submissions() As String

'Code to run when the form activates
Private Sub UserForm_Activate()
    'When this userform is first initialized set up the array
    'as a one dimensional array with a single element
    ReDim submissions(0 To 0)
End Sub

'Code to run when the commandbutton1 is clicked
Private Sub CommandButton1_Click()
    'call the addSubmission sub
    addSubmission
End Sub

'Code to run when the commandButton2 is clicked
Private Sub CommandButton2_Click()

    'add submission one more time
    addSubmission

    'Now Loop through the array and send the values out
    'to the worksheet
    Dim rowCounter As Long
    rowCounter = 1
    For Each submission In submissions
        Sheet1.Cells(rowCounter, 1).Value = submission
        rowCounter = rowCounter + 1
    Next submission

    'Now close the form
    Me.Hide

    'And activate sheet1 for the user to see their submissions
    Sheet1.Activate
End Sub

'addSubmission will add textBox1 value to
'  the submissions array declared at the
'  top of this userform code.
Sub addSubmission()
    'First we have to redim the array to hold the new
    '   submission.
    'But only redim it if this isn't the first submission
    If UBound(submissions) > 0 Or submissions(0) <> "" Then ReDim Preserve submissions(0 To UBound(submissions) + 1)
    submissions(UBound(submissions)) = Me.TextBox1.Value

    'Clear the textbox so the user doesn't have to backspace
    Me.TextBox1.Value = ""
End Sub