How to populate a userform combobox from a table in the same document

jrooney7 · #1 04-13-2019, 03:54 PM

Hello again,

I have uploaded a dotm that has a series of tables. I would like to populate a combobox on a userform from the text in the second column of the first table of the document. This first table will have a different number of rows depending on user entry. The code I have so far is below, and it is recognizing the length (number of rows) in Table 1, as the combobox is growing appropriately, but it is blank (not pulling the text from column 2).

Code:

Private Sub UserForm_Initialize()

    Dim oTable As Table
    Dim i As Long, j As Long, m As Long
    Dim myArray() As Variant
    Dim oData As Range
        Set oTable = ActiveDocument.Tables(1)
        i = oTable.Rows.Count
        ReDim myArray(i, 2)
            For m = 1 To i
                Set oData = oTable.Cell(m, 2).Range
                If oData.Text <> "" Then
                    myArray(m, 2) = oData.Text
                Else
                    myArray(m, 2) = " "
                End If
            Next m
    ItemComboBox.List = myArray

End Sub

Any thoughts as to what I'm doing wrong?

I would also like a textbox on the same userform be filled with text from the fourth column of the first table according to which entry was chosen for the above-mentioned combobox. So if the user selects the entry from the third row, the text from cell(3, 4) would be entered into the textbox. The first table is the Item Submitted png and the result I'm trying to get to is the End Result png.

I have uploaded a sample of the document with entries in the first table and I have entered the above code into the SNR Userform initialize event.

gmayor · #2 04-13-2019, 08:45 PM

The following should do it

Code:

Private Sub UserForm_Initialize()

Dim oTable As Table
Dim i As Long, m As Long
Dim oData As Range
    Set oTable = ActiveDocument.Tables(1)
    i = oTable.Rows.Count
    For m = 1 To i
        Set oData = oTable.Cell(m, 2).Range
        oData.End = oData.End - 1
        If oData.Text <> "" Then ItemComboBox.AddItem oData.Text
    Next m
    ItemComboBox.AddItem "[Select Item]", 0
    ItemComboBox.ListIndex = 0
End Sub

jrooney7 · #3 04-14-2019, 06:30 PM

That was perfect! I added an AfterUpdate event to that combobox to pull the corresponding item description. In case anyone needs it:

Code:

Private Sub UserForm_Initialize()

    Dim oTable As Table
    Dim i As Long, m As Long
    Dim oData As Range
        Set oTable = ActiveDocument.Tables(1)
        i = oTable.Rows.Count
        For m = 1 To i
            Set oData = oTable.Cell(m, 2).Range
            oData.End = oData.End - 1
            If oData.Text <> "" Then ItemComboBox.AddItem oData.Text
        Next m
        ItemComboBox.AddItem "[Select Item]", 0
        ItemComboBox.ListIndex = 0
    
End Sub

Private Sub ItemComboBox_AfterUpdate()

    Dim oTable As Table
    Dim i As Long, m As Long
    Dim oData As Range
    Dim sLastChar As String
    Dim sCellText As String
        Set oTable = ActiveDocument.Tables(1)
        i = oTable.Rows.Count
        For m = 1 To i
        Set oData = oTable.Cell(m, 2).Range
            oData.End = oData.End - 1
        sCellText = oTable.Cell(m, 4).Range.Text
        sLastChar = Right(sCellText, 1)
            If ItemComboBox.Text = oData.Text Then
                If sLastChar = Chr(7) Or sLastChar = Chr(13) Then
                    sCellText = Left(sCellText, Len(sCellText) - 2)
                    sLastChar = Right(sCellText, 1)
                End If
                ItemDescriptionTextBox.Value = sCellText
            End If
        Next m

End Sub

Thank you for your help!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to populate a userform combobox from an excel worksheet	jrooney7	Word VBA	14	09-16-2018 08:52 PM
Userform with cascade ComboBox	thovdael	Excel Programming	7	04-19-2017 08:01 PM
Populate Combobox from Excel into a Word UserForm	ferguson4848	Word VBA	3	10-28-2016 09:05 PM
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
Value of Combobox in other userform	Vibov	Excel Programming	4	11-19-2015 04:12 AM