View Single Post
 
Old 04-04-2016, 01:46 AM
highrise955 highrise955 is offline Windows 10 Office 2013
Advanced Beginner
 
Join Date: Mar 2016
Posts: 37
highrise955 is on a distinguished road
Default UserForm Dropdown List Not Populating

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?
Reply With Quote