Adding to sheet from Combo Box and Command Button VBA

Question

I'm trying to design a user form that currently looks something like this:

The Combo Box uses A1:A5 to generate its list and then whatever is selected is linked into cell D12.

However I want the user to be able to populate a new list based on the options in the combo box by using the Add command button.

So for example the user would select test2 from the combo box, click Add and then select another option, say test4, click Add again and it would appear beneath test2 in cell D13.

I've used VBA before but never really for Form Controls, only for manipulating data so this is fairly new to me. Most of the tutorials online can't seem to help either, anyone have any suggestions please?

Sun Sun · Accepted Answer · 2016-06-03T12:36:20

This code goes into a separate Module:

Sub add_click()

Dim aws As Worksheet
Dim i As Integer
Dim firstemptyrow As Integer

Set aws = ActiveSheet

    With aws

        firstemptyrow = .Cells(.Rows.count, "D").End(xlUp).Row + 1
        If firstemptyrow < 12 Then firstemptyrow = 12

        i = .DropDowns("dropdown1").Value
        .Range("D" & firstemptyrow).Value = .DropDowns("dropdown1").List(i)

    End With

End Sub

You need to substitute "dropdown1" with the name of your ComboBox. Afterwards you assign this macro to your button.

Adding to sheet from Combo Box and Command Button VBA

1 Answers