# Excel - Dropdown List in a UserForm



## CWDENVER (Dec 1, 2006)

I'm trying to have dropdown lists in my userforms and they're just not working. As an example, can someone tell me how to have a dropdown box in a userform with all the states in it? Thanks.


----------



## cwwozniak (Nov 29, 2005)

I am Using Excel 2003 and just followed the directions in the help file for "Enter data in a cell from a list you specify" to add a drop down states list for cell B1 in the attached file.

Am I missing something that you need done with the list?

EDIT: I did a quick copy/paste from a list of states I found on a web site. Looks like all of the two word state names lost the first word in the process. Sorry about that. The file should still give you a general idea of how to do the list.


----------



## Ken Puls (Jan 28, 2006)

Hi there,

A userform combobox? Try this, where your data is in range A1:A15 on the worksheet:

```
Private Sub UserForm_Initialize()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To populate a combobox with data from
'               a worksheet range
    
    Dim cbtarget As MSForms.ComboBox
    Dim rngSource As Range
    
    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Sheet1").Range("A1:A15")
    
    'Fill the listbox
    Set cbtarget = Me.ComboBox1
    With cbtarget
        'Insert the range of data supplied
        .List = rngSource.Cells.Value

    End With
    
End Sub
```
Adapted from an example on my site here of filling a multi column listbox with data from an Excel range.

HTH,


----------



## CWDENVER (Dec 1, 2006)

I tried copying your code and making the adjustments I need, but I get an error saying "Improper use of the Me keyword". What does that do and is there another way to accomplish what I need? Thanks, you've been very helpful.


----------



## Ken Puls (Jan 28, 2006)

Are you using this in a userform or an Excel worksheet? The two are very different items...


----------



## CWDENVER (Dec 1, 2006)

The dropdown is in a userform, not a cell. I tried typing in a range into a worksheet and referencing that in the macro, and I've tried doing it all in a macro, but neither work.


----------



## Zack Barresse (Jul 25, 2004)

Alter Ken's code slightly and use ...


```
Private Sub UserForm_Initialize()

    With cbTarget 'assumes you have made this the name of the control 
                   ' in your userform (right click | properties | name)
       
        .Additem "Alabama"
        .Additem "Arkansas"
        .Additem "etc.."

    End With
    
End Sub
```
If you have these values in a range, set the range like Ken has, then loop through the range, adding the items to the combobox list.

HTH


----------



## CWDENVER (Dec 1, 2006)

Ok, I've asked for help on this several times, but everytime I've gotten feedback and tried it, it did not work. I've tried to piece together a small example of what the help file says and feedback I've gotten on here. I thought this would generate the dropdown list when I clicked on the combobox, but it didn't work. Can someone help me get this right? Thanks.


```
Private Sub ComboBox1_Change()

ComboBox1.AddItem "Ex. 1"
ComboBox1.AddItem "Ex. 2"
ComboBox1.AddItem "Ex. 3"

ComboBox1.DropDown

End Sub
```


----------



## Zack Barresse (Jul 25, 2004)

I'm guessing that this is on a worksheet and not on a Userform.. If this is the case, right click your control (in design mode) and select Properties. Take a look at the list fill range. If this is not the case, can you upload a small sample?


----------

