Excel VBA programatic drop down list

Hello tmonteit,

You can use the following code in the "Private Sub Worksheet_Change(ByVal Target As Range)" worksheet procedure.  This sub fires when you change a cell (type something in and press <Enter> or otherwise leave the cell), and it gives you the address of the cell that was changed.

Code in there will fire every time you change any cell, but we can tell it that we're only interested in certain cells changing; for example, say you want to type "colors" or "volume" in cell A1 to get one of the drow-down menus you mention above to appear in cell C1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'If it wasn't cell A1 that was changed, the rest is ignored
    Target.Offset(0, 2).Select
    With Selection.Validation
        .Delete
            If Target = "colors" then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="Red,Amber,Green"
            Else
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="High,Med,Low"
            End If
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If
End Sub

Hope that helps!