How do I create a combobox in active cell?

Question

I want to create a script, that selects the first blank row, and then places a drop down in the "A" column of that row. I then want to use .additem("") to add the items that I need to it. How would I do this?

Thanks in advance.

Answer 1

Try this

Sub AddComboBox(Col As Long)
    Dim LastRow As Range

    With ThisWorkbook.ActiveSheet
    Set LastRow = .Cells(.Rows.Count, Col).End(xlUp)
    End With

    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                                    Link:=False, _
                                    DisplayAsIcon:=False, _
                                    Left:=0, _
                                    Top:=LastRow.Top + LastRow.Height, _
                                    Width:=100, _
                                    Height:=16)
        With .Object
            .AddItem "Hi"
        End With
    End With
End Sub

15 is the default row height. All bets are off when your rows have different heights.

Edit 1: As per your comment, you can use this as much as you want by calling it from another sub. I've added a parameter to the first sub called Col which is the Id of your column.

Sub Main()
Dim I As Long
For I = 1 To 3
AddComboBox I
Next
End Sub

Edit 2: I've incorporated dee's productive comment that handles variable rows height.