Form control combo box

It would be easier if you attached an example workbook.

The Worksheet_Change procedure will repopulate Drop Down 1 when you make a change to cell A1. It loops through cells B1:B9 and adds items excluding blanks. A similar code block could be added to the procedure for a additional drop downs.

The Worksheet_SelectionChange procedure will "Advance" the drop down selection to the next item when you select cell D1.

To install the code:

• Right-click on the sheet tab.
• Select View Code from the pop-up context menu.
• Paste the code from below in the worksheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Populate excluding blanks
    Dim dd As DropDown, cell As Range
    If Target(1).Address(0, 0) = "A1" Then
        Set dd = Me.DropDowns("Drop Down 1")
        dd.ListFillRange = ""
        dd.RemoveAllItems
        For Each cell In Range("B1:B9")
            If cell <> "" Then dd.AddItem cell.Value
        Next cell
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'advance
    Dim dd As DropDown, ndx As Long
    If Target(1).Address(0, 0) = "D1" Then
        Set dd = Me.DropDowns("Drop Down 1")
        ndx = dd.ListIndex
        If ndx = dd.ListCount Then ndx = 1 Else ndx = ndx + 1
        dd.ListIndex = ndx
    End If
End Sub

Originally Posted by copernicus

Thanks. I think I understand whats going on and how to modify it, but Im getting a compile error "invalid use of me keyword". I placed your code in Module 1. Wrong place?

Yes, wrong place. See the "To install the code" instructions in the post.

AlphaFrog's Macro is sheet specific.

Right Click on the sheet name at the bottom of excel and select view code.

Paste the code into the module that opens and close it.