I have a userform called "DrawingNumberEntryForm" with a ComboBox control called "DrawingNumberUf". I am trying to populate the control with a list from an Excel sheet.
Here is my code...
Code:
Private Sub DrawingNumberEntryForm_Initialize()
Dim varData As Variant
Dim lngIndex As Long
Dim oCC As ContentControl
varData = LoadFromExcel_ADODB("c:\temp\ItemSheet.xlsx", "Item Sheet")
Set oCC = .SelectContentControlsByTitle("DrawingNumberUf").Item(1)
'Set oCC = ActiveDocument.SelectContentControlsByTag("Tag").Item(1)
With oCC
For lngIndex = .DropdownListEntries.Count To 1 Step -1
.DropdownListEntries(lngIndex).Delete
Next lngIndex
For lngIndex = 0 To UBound(varData, 2)
.DropdownListEntries.Add varData(0, lngIndex), varData(1, lngIndex)
Next lngIndex
End With
lbl_Exit:
Exit Sub
End Sub
Function LoadFromExcel_ADODB(ByRef strSource As String, _
strRange As String, Optional bIsSheet As Boolean = True, _
Optional bSuppressHeadingRow As Boolean = True)
Dim oConn As Object
Dim oRecSet As Object
Dim strConnection As String
Dim lngCount As Long
If bIsSheet Then
strRange = strRange & "$]"
Else
strRange = strRange & "]"
End If
Set oConn = CreateObject("ADODB.Connection")
If bSuppressHeadingRow Then
'Suppress first row.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strSource & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
'No suppression.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strSource & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If
oConn.Open ConnectionString:=strConnection
Set oRecSet = CreateObject("ADODB.Recordset")
'Read the data from the worksheet/range.
oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRecSet
.MoveLast
'Get count.
lngCount = .RecordCount
.MoveFirst
End With
LoadFromExcel_ADODB = oRecSet.GetRows(lngCount)
'Cleanup
If oRecSet.State = 1 Then oRecSet.Close
Set oRecSet = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
lbl_Exit:
Exit Function
End Function
No matter what I do I can't get the ComboBox to populate. The same code (slightly altered and thanks to macropod and gmaxey!


)) works perfectly fine in the main document. Can anyone spot anything obvious that I am doing incorrectly?