1

I am new to vba, I would like to ask how can I paste the item I am selecting from combobox in each cell?

Example: Combobox2 items are 10-STD, 12-40, 8-STD.

I will select 10-STD and it should paste on column E9, next I will select 12-40 it should paste on the next empty cell E10, same thing with 8-STD when selected it should paste on E11.

Thanks for the kind help.

  • There are 2 types of combo boxes. Is this a **form control** combo box, or an **ActiveX control** combo box? (I have a detailed description of the difference [here](https://stackoverflow.com/a/49263001/8112776).) – ashleedawg Apr 01 '18 at 13:30

1 Answers1

0

For the Form Control "Combo Box" the following code will work:

Sub DropDown1_Change()
    Dim dD As Object
    Dim selectedValue As String

    Set dD = DropDowns("Drop Down 1")

    selectedValue = dD.List(dD.ListIndex)

    If Range("E9") = Empty Then
        Range("E9") = selectedValue
    Else
        lrow = Cells(Rows.Count, 5).End(xlUp).Row
        Cells(lrow + 1, 5) = selectedValue
    End If  
End Sub

For the ActiveX ComboBox it is the following:

Private Sub ComboBox1_Change()
    Dim cB As ComboBox
    Dim selectedValue As String

    Set cB = OLEObjects("ComboBox1").Object

    selectedValue = cB.Value

    If Range("E9") = Empty Then
        Range("E9") = selectedValue
    Else
        lrow = Cells(Rows.Count, 5).End(xlUp).Row
        Cells(lrow + 1, 5) = selectedValue
    End If
End Sub

EDIT: As QHarr said, the code can be enbeeded in the Worksheet Code, so it works without Dim ws As Worksheet Set ws As ActiveWorksheet Code. Also Range("E9") = Empty can be replaced by isEmpty(Range("E9"))

FloLie
  • 1,820
  • 1
  • 7
  • 19
  • 1
    could use IsEmpty(ws.Range("E9")) Then ..... don't know if more efficient. And whole thing could be within With ws so then use .Cells? – QHarr Apr 01 '18 at 14:58
  • Thanks for the comment QHarr. For the IsEmpty, i think it doesn't matter efficiency wise, but is equaly applicable. To put it in the Worksheet code is actually a really good idea and I added it the the post. Thank you and +1 – FloLie Apr 01 '18 at 15:15