Link to home
Start Free TrialLog in
Avatar of tmonteit
tmonteitFlag for Afghanistan

asked on

Excel VBA programatic drop down list

I'm using MS Excel 2003.

I want to programatically add a drop down list to a cell.
   DROP DOWN:  ("RED", "AMBER", "GREEN")
   DROP DOWN:  ("HIGH", "MEDIUM", "LOW")

I'm testing 2 conditions when I enter the worksheet.  Depending on the condition, I want a dropdown list in a given cell.

Pseudo Code:  Cell.Offset(0,2)."Add Drop Down", { Red, Amber, Green}

Looking for simple example at beginner level.
Avatar of Jeroen Rosink
Jeroen Rosink
Flag of Netherlands image

Did you try to work wwith validaiton lists?
Asume thie following:
1. Value which results in different list value = Cell A1
2. you have 2 list, use defined name like: COLOR and SEVERITY
3. create these defined lists
4. asume that you need in cell B1 the dropdown
5. select B1: from menu: Data>>Validation>>Allow List
6. Enter the formula: =IFA1=1,COOR,SEVERITY)

now if A1 contains a 1 then  the colors are shown, otherwise the HIGH, MEDIUM and low will be shown.

I hope this works for you
regards,
Jeroen
ASKER CERTIFIED SOLUTION
Avatar of grendel777
grendel777
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Set rg = Intersect(Target, Range("A1"))
If rg Is Nothing Then Exit Sub

'If it wasn't cell A1 that was changed, the rest is ignored
    'Target.Offset(0, 2).Select
    With [C1].Validation
        .Delete
        Select Case UCase(Target)
        Case "COLORS"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="Red,Amber,Green"
        Case "SEVERITY"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="High,Med,Low"
        End Select
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    'End If
End Sub

gots triggered  on words in cell A1: COLOR or severity

regards,
Jeroen
found an error when you enter something not corresponding the values
Private Sub Worksheet_Change(ByVal Target As Range)
Set rg = Intersect(Target, Range("A1"))
If rg Is Nothing Then Exit Sub

'If it wasn't cell A1 that was changed, the rest is ignored
    'Target.Offset(0, 2).Select
    With [C1].Validation
        .Delete
        Select Case UCase(Target)
        Case "COLORS"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="Red,Amber,Green"
        Case "SEVERITY"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="High,Med,Low"
        Case Else
            Exit Sub
        End Select
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    'End If
End Sub
tmonteit,

You haven't said it but it looks as if you want the first dropdown to control the contents of the second dropdown. If that's what you want then here's a file showing how it can be done:

Zipped MSWord file
http://download.yousendit.com/089BCB1B68EDF1B0 

Ordinary MSWord file
http://download.yousendit.com/A23876061150CE3E

Hope that helps

Patrick

how about this one ?

here we go...

i type in cell
A1 : experts-exchange.com
B1: gmail.com
C1: yahoo.com
D1: msn.com

and i will display dropdown in B4 ( you can choose any cell where you like to display drop down)

go to Data > Validation and select List from Validation Criteria and in source type :
=A1:E1
thats it