#1
|
|||
|
|||
How to populate a userform combobox from a table in the same document
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 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. Last edited by jrooney7; 04-13-2019 at 07:34 PM. Reason: Added additional process |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
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 |
|
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 |