如何在VBA中将ComboBox选项存入变量并写入活动单元格及清空复用

该UserForm包含两个下拉框（分别用于选择姓名和年龄）以及一个提交按钮，用户可通过选择选项并点击按钮，将拼接后的内容写入预先选中的活动单元格，完成后窗体保持打开状态，下拉框选中状态清空以便继续操作。

原UserForm代码 #

Private Sub ComboBox2_Change()
   ' Call MainValuesInForm 'use this if you want to trigger after selection of the value
End Sub

Private Sub ComboBox3_Change()
  '  Call MainValuesInForm 'use this if you want to trigger after selection of the value
End Sub

Private Sub MainValuesInForm()
    Dim Name As String
    Dim Age As String
    
    Name = Me.ComboBox2.Value
    Age = Me.ComboBox3.Value
    
    ActiveCell.Value = Name & " " & Age
End Sub

Private Sub SubmitButton_Click()
    Call MainValuesInForm 'or use this if you want to trigger by means of the 'Submit' (or Insert) button
End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBox2
        .Clear
        .AddItem "Joe"
        .AddItem "Jack"
        .AddItem "Dan"
    End With
    
    With Me.ComboBox3
        .Clear
        .AddItem "30"
        .AddItem "40"
        .AddItem "50"
    End With
End Sub

理想操作流程 #

• 用户预先选中一个活动单元格，运行宏打开窗体
• 在两个下拉框中分别选择姓名和年龄选项
• 点击提交按钮，拼接后的姓名 年龄内容写入活动单元格
• 写入完成后，窗体保持打开，两个下拉框的选中状态被清空，可继续选择其他选项写入到其他活动单元格

实现需求的修改后代码 #

只需调整MainValuesInForm过程，增加输入校验并在写入后清空下拉框选中状态：

Private Sub MainValuesInForm()
    Dim Name As String
    Dim Age As String
    
    ' 校验是否已选择完整内容
    If Me.ComboBox2.ListIndex = -1 Or Me.ComboBox3.ListIndex = -1 Then
        MsgBox "请先选择姓名和年龄！", vbExclamation
        Exit Sub
    End If
    
    Name = Me.ComboBox2.Value
    Age = Me.ComboBox3.Value
    
    ActiveCell.Value = Name & " " & Age
    
    ' 清空下拉框选中状态
    Me.ComboBox2.ListIndex = -1
    Me.ComboBox3.ListIndex = -1
End Sub

Private Sub ComboBox2_Change()
   ' Call MainValuesInForm 'use this if you want to trigger after selection of the value
End Sub

Private Sub ComboBox3_Change()
  '  Call MainValuesInForm 'use this if you want to trigger after selection of the value
End Sub

Private Sub SubmitButton_Click()
    Call MainValuesInForm
End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBox2
        .Clear
        .AddItem "Joe"
        .AddItem "Jack"
        .AddItem "Dan"
    End With
    
    With Me.ComboBox3
        .Clear
        .AddItem "30"
        .AddItem "40"
        .AddItem "50"
    End With
End Sub

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