How to store selections in comboboxes to variables in vba-CodePudding

I have the following UserForm:

Where a user selects from the dropdown their name and age, this in MyForm(Code)

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

To be clear, the user would have an activecell then run the macro. Ideally, the user would make a selection in the two comboboxes, press insert, the concatenation is written to the activecell. Then once written the form remains open and selection in the comboxes goes to blank so the user can continue inserting data into different activecells

I'm trying to take the selection from the two comboboxes in the form and then concatenate these values and place them in the activecell

CodePudding user response：

try this:

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