Using Combobox for a Dropdown list on a Userform

[andrewb90]

Hi all,

I've been looking up how to do this online, and I haven't had much success, so hopefully someone can point me in the right direction.

I am trying to create a few drop down boxes on a userform, and am trying to make the values a range from my sheet. The three drop downs will be for Month, Day, and Year.
Is there a simple way to do this, that I just haven't found yet?

 

[CheekyDevil]

Hi andrewb90

You can fill a combobox with a list range using the following code:

Dim c As Range
ComboBox1.Clear 'make sure this is the right combobox name
With Worksheets("(SHEETNAME)") 'change this to your sheets name
    For Each c In .Range(.Range("LISTRANGE"), .Range("A" & .Rows.Count).End(xlUp)) 'replace LISTRANGE with your list's range, I,e. ("A1:A10")
        If c.Value <> vbNullString Then ComboBox1.AddItem c.Value 'Again make sure this is the right combobox name
    Next c
End With
End Sub

Just be sure to change the SHEETNAME to your own name, and make sure ComboBox1 is the one you want to populate.

This code will also remove all the blanks from the bottom of the list, so all you need to do to update it is add items to your spreadsheet and they'll be populated in the combobox once refreshed

 

[My Aswer Is This]

You said:

I am trying to create a few drop down boxes​

I assume you mean Combobox is that correct?

So tell me the name of these three comboboxes
And get values loaded into the combobox from what range?

Give specific details like from column 1 or column 2
Do not say column Month

 

[My Aswer Is This]

To load a combobox with values from the active sheet column A
Put this script in your Userform.
The combobox will be loaded with values when you open your userform
Change Combobox name to your needs.

Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Range("A1:A" & Lastrow).Value
End Sub

 

[andrewb90]

Can I use a named range for each combo box?
Combobox1 would have a list from range "MONTHS"
Combobox2 would have a list from range "DAYS"
Combobox3 would have a list from range "YEARS"

Additionally, the location of the named range in the workbook wouldn't matter right?

 

[My Aswer Is This]

Do it like this:
Just so the named Range is in the active Workbook

Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Range("MONTHS").Value
ComboBox2.List = Range("DAYS").Value
ComboBox3.List = Range("YEARS").Value
End Sub

 

[andrewb90]

I'm getting an error, and I can't tell what's causing it.
Run Time error 104: Method 'Range' of Object'_global' failed

 

[My Aswer Is This]

This part of the script is very basic
I suspect your range is not named exactly correct.

Or change

ComboBox2
to:

Combobox​

B or b_{^{<strike>
</strike>}}​