Load a Table Array into a one Column Combo Box

[Johnny]

I need VBA code that loads all of the values contained in Row 1 of Table 2
into ComboBox 1 when the form that contains the combox intitializes. The
number of columns is variable.

 

[Johnny]

I may need clarify that I would like the data found in row 1 of the table to
be listed in one column in the ComboBox.

 

[Lene Fredborg]

You can use something like the following:

Private Sub UserForm_Initialize()
Dim oCell As Cell

With ComboBox1
For Each oCell In ActiveDocument.Tables(2).Rows(1).Cells
'Add text of cell - excl. cell marker
.AddItem Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
Next oCell
'Set default value
.Value = "Please select a value"
End With
End Sub

The code above displays the text "Please select a value" as the default
value. Since that value is not added to the list, it will not be available
once the user has selected another value. You can insert one of the values
from the cells as the default instead if you wish. To insert the first item
in the list (count starts at 0):

.Value = .List(0)

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[Johnny]

That worked great! Thank you so much. I have a follow up question you may
be able to help me with based on the ComboBox1 results.

The header Row (Row 1) on my table lists the Department Names which
popluated in ComboBox1. I have a second ComboBox(2) set up to load the names
of the employees based on the Dept name selected. I have the employee names
hard coded the VBA code but I want be able to load them into ComboBox2 from
the table based on the department name. The main reason is that it's easier
to add and delete names from the table. The names of the employees are
listed under each header (dept).

Thanks again for your help.

 

[Doug Robbins - Word MVP]

See the Cascading Listboxes section of the
following page on fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Johnny]

I looked at that site and didn't see quite what I was after or a least it was
beyond my comprehension to adapt the code to my needs.

The current code I'm using is:

Select Case UserForm13.ComboBox1
Case "Accounting"
Me.ComboBox2.List = Split("Greg,Amy,Ted,Hopper", ",")
Case "Purchasing"
Me.ComboBox2.List = Split("Janet,Quincy", ",")
Case "Sales"
Me.ComboBox2.List = Split("Dee,Bob,Sue,Sally,Dennis,Ted,Michael", ",")
Case "Margeting"
Me.ComboBox2.List = Split("Jane,John,Paul", ",")
Case "Mail Room"
Me.ComboBox2.List = Split("Margie,James,Hunter,Chris,Mark,Robert", ",")
End Select


What I'm after is to autopopluate ComboBox2 with a list of employees from
the table based on the Dept. The employees are listed below each department
with the dept name as the header.

Please forgive me if my answer could be found in the webside you directed me
to. Any further help you could provide would be appreciated. Thanks.

 

[Johnny]

Just some further clarification. If comboBox1 = Accounting, I would like
ComboBox2 to populate only with the employees listed under "Accounting" on
the table which is in the very first cell. The dept. names will never change
location on the table. This will be a one column ComboBox.

 

[Lene Fredborg]

Below you will find code that should do what you want. The methods I used may
not be the same as suggested in the linked article but it should work.

The code runs whenever the user changes the value of ComboBox1. Empty cells
in a column will be skipped (I suppose there will be empty cells due to
different numbers of employees in the departments).


Private Sub ComboBox1_Change()

'Insert values in ComboBox2 depending on the value selected in ComboBox1
'Table 2 column number to use is list item no. + 1 (because list count
starts at 0)
'Add values from row 2 and downwards in Table 2

Dim oCell As Cell
Dim n As Long
Dim strName As String

'Find column to look in - value in Combobox1 determines which column to
use
n = ComboBox1.ListIndex + 1

With ComboBox2
'Delete existing items
.Clear
For Each oCell In ActiveDocument.Tables(2).Columns(n).Cells
'Skip first row
If oCell.RowIndex > 1 Then
'Get text from cell - excl. cell marker
strName = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
'Only add item if the result is not "" (empty cell)
If strName <> "" Then
.AddItem strName
End If
End If
Next oCell
'Set default value (could also be e.g. .List(0) - i.e. first name)
.Value = "Select a name"
End With
End Sub


--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[Johnny]

I'm getting the following error message:

"Requested member of the collection does not exist"

The following syntax is highlighted:

For Each oCell In ActiveDocument.Tables(2).Columns(n).Cells

 

[Lene Fredborg]

Sorry, I tested the code while my ComboBox1 had a "real" default value. If
you have set a default value as I suggested (like "Please select a value"),
the code will point to a non-existing column 0 in Table 2 when the UserForm
initializes - and an error occurs. Below, you will find an adjusted version
of the code. The only difference is that an If - End If constructions has
been added, testing whether n (the column number) is larger than 0. Hope it
works:


Private Sub ComboBox1_Change()

'Insert values in ComboBox2 depending on the value selected in ComboBox1
'Table 2 column number to use is list item no. + 1 (because list count
starts at 0)
'Add values from row 2 and downwards in Table 2

Dim oCell As Cell
Dim n As Long
Dim strName As String

'Find column to look in - value in Combobox1 determines which column to
use
n = ComboBox1.ListIndex + 1

With ComboBox2
'Delete existing items
.Clear
'n = 0 if a ComboBox1 shows a default value that is not in the list
If n > 0 Then
For Each oCell In ActiveDocument.Tables(2).Columns(n).Cells
'Skip first row
If oCell.RowIndex > 1 Then
'Get text from cell - excl. cell marker
strName = Left(oCell.Range.Text, Len(oCell.Range.Text) -
2)
'Only add item if the result is not "" (empty cell)
If strName <> "" Then
.AddItem strName
End If
End If
Next oCell
'Set default value (could also be e.g. .List(0) - i.e. first name)
.Value = "Select a name"
End If
End With
End Sub

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[Johnny]

That worked. Thanks for you patience. I learn a lot.