?
Solved

Excel VBA programatic drop down list

Posted on 2007-10-18
6
Medium Priority
?
19,897 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:tmonteit
6 Comments
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20106753
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
0
 
LVL 6

Accepted Solution

by:
grendel777 earned 2000 total points
ID: 20106764
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!
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20107156
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20107166
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20107458
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

0
 
LVL 20

Expert Comment

by:Gawai
ID: 20115488
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question