none
Populate Word Userform MultiColumn ComboBox from NamedList in Excel. RRS feed

  • Question

  • Hello,

    I am trying to populate a multicolumn combobox on a userform for a word document template "\Letter.dotm".  The source for the combobox is a named list on a excel worksheet "\cbo.xlsm".  The following links were helpful:

    http://www.contextures.com/Excel-VBA-ComboBox-Lists.html
    http://www.fontstuff.com/vba/vbatut09a.htm

    The NamedList "SSIC" works well and Refers to: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1) 

    Thus far, I have managed to get a multicolumn ComboBox on the excel userform to work as follows:

    Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim cLoc As Range
        Set ws = Worksheets("Data")
         For Each cLoc In ws.Range("SSIC")
           With cbo_ssic
            .AddItem cLoc.Value
            .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
           End With
         Next cLoc
    End Sub

    The word document template "\Letter.dotm" is not behaving as excpected.  References to Microsoft Excel 14.0 Oobject Library have been enabled, but .Value and .Offset inside the With statement are not being recognized.  Please see below:

    Private Sub UserForm_Initialize()
    Dim xlApp As Object
    Dim wb As Object
    Dim ws As Worksheet
    Dim cLoc As Range
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set wb = xlApp.Workbooks.Open(ThisDocument.Path & "\cbo.xlsm")
    Set ws = wb.Worksheets("Data")
        For Each cLoc In ws.Range("SSIC")
            With cbo_ssic
                .AddItem cLoc.Value
                .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
            End With
        Next cLoc
    wb.Close
    End Sub

    Could someone please advise me on how to fix this or populate the word userform combobox from an excel list.

    Thanks,

    Andrew

    Thursday, August 15, 2013 5:59 PM