Microsoft Office Forums - View Single Post - [Solved] UserForm Dropdown List Not Populating

highrise955 · 04-05-2016, 01:45 AM

Quote:

Originally Posted by gmayor

You are trying to use code intended for a content control with a userform combobox.
See http://www.gmayor.com/Userform_ComboBox.html which includes code to populate a list box or combobox from an Excel worksheet.

Quote:

Originally Posted by highrise955

Damn it! I actually had that code from another thread you and macropod helped me with but I chose to try to just duplicate what I did in my main document.

Also feel pretty foolish about the "not a content control" thing as well.

Thanks gmayor, I will give it a shot.

Hi Graham!

Your code works fine for me if I define the range as a worksheet. However, when I specify range as a "Named Range" I get the following error...

Here is my code...

Code:

Private Sub Userform_Initialize()

xlFillList ListOrComboBox:=Me.DrawingNumberUf, _
iColumn:=1, _
strWorkbook:="C:\Temp\ItemSheet.xlsx", _
strRange:="CollRange", _
RangeIsWorksheet:=False, _
RangeIncludesHeaderRow:=True

End Sub


Sub xlFillList(ListOrComboBox As Object, _
iColumn As Long, _
strWorkbook As String, _
strRange As String, _
RangeIsWorksheet As Boolean, _
RangeIncludesHeaderRow As Boolean)




If RangeIsWorksheet = True Then strRange = strRange & "$]"

Set CN = CreateObject("ADODB.Connection")


If RangeIncludesHeaderRow Then
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If


Set RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = 3

RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 'read the data from the worksheet

...
...

Any ideas on what I'm doing wrong?