VBA Word For Each Loop through Comboboxes to Populate Word Table

[lewiskyler]

Hi there, thank you so much in advance for your help... Also sorry in advance for the long post but I figure it will be easier to help if my intentions are thoroughly described.
I am writing a macro in MS Word 2007 that will create what's known as a "Team Chart" which is essentially a Word table. Each row in the table is a different person with picture, bio, and contact details as the columns. The Macro will populate the Word table by pulling info from a separate "master" document Word table. The macro pulls information from the master based on user input from comboboxes.
When the macro runs, it opens a new Word document and inserts a table (it also opens the "master document") and then displays a dialogue box with comboboxes (drop down menus). The options in each combobox will be about 100 names. The user selects a name for each combobox in the order they want them to appear on the table. The user then clicks the command button and the macro identifies the name, pulls their information from the master Team Chart, and pastes it into the appropriate row in the new table.
For each combobox entry, I want to use an if statement to cycle through the possible names that the user suggested to match the selected person with the appropriate action (i.e. pulling their info from the master). I want to use a for each loop to cycle through the combobox entries.
Although I'm not sure this is the best way to do it, I'm trying to set up a two-dimensional array that is populated by looping through the comboboxes. For each combobox in this array, the first dimension will be the TabIndex of the combobox and the second dimension will be the Value of the entry in the combobox (i.e. the selected name). The Value part of the array will identify the appropriate person in the if statement mentioned above. The TabIndex part of the array will be used to select the correct row in the new table to insert the information copied from the master.

Here is the code I have so far (userform code)

Private Sub CommandButton1_Click()
Dim MyApp As Word.Application
Dim Master As Document
Dim Doc As Document
Dim combo As Control
Dim i As Long
Dim temp1 As Variant
Dim temp2 As Variant
Dim arrCtrls()
Dim arrfinal()
Dim arrvalues()
Set Master = Documents.Open("S:\PRODUCTION RESOURCE GROUP\Personal Folders\Lewis\teamchartmaster.docx")

For Each combo In Me.Controls
If TypeName(combo) = "ComboBox" Then
i = i + 1
ReDim Preserve arrCtrls(1 To 2, 1 To i)
arrCtrls(1, i) = combo.TabIndex 'In 1st dimension of array
arrCtrls(2, i) = combo.Value
End If
Next combo

temp1 = arrCtrls(2, i) 'Not sure if this is correct

ReDim arrfinal(1 To UBound(arrCtrls(), 1))
For i = 1 To UBound(arrCtrls, 1)
arrfinal(i) = arrCtrls(1, i)
Next i

ReDim arrvalues(1 To UBound(arrCtrls(), 2))
For i = 1 To UBound(arrCtrls, 2)
arrvalues(i) = arrCtrls(2, i)
Next i

For Each combo In Me.Controls
If TypeOf combo Is Combobox Then
For i = 1 To 100
If temp1 = "Richard Roderick" Then
Documents(1).Tables(1).Rows(i).Cells(1).Range.Text = Master.Tables(1).Rows(1).Cells(2).Range
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range.Collapse wdCollapseStart
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range _
.InlineShapes.AddPicture FileName:="S:\PRODUCTION RESOURCE GROUP\Personal Folders\Lewis\Stars Behavioral Group\Rroderick.jpg"
Master.Tables(1).Rows(1).Cells(3).Range.FormattedText.Copy
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(2).Range.PasteAndFormat (wdFormatOriginalFormatting)
End If

If temp1 = "Mark Pastorius" Then
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(2).Range.Text = Master.Tables(1).Rows(2).Cells(2).Range
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range.Collapse wdCollapseStart
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range _
.InlineShapes.AddPicture FileName:="S:\PRODUCTION RESOURCE GROUP\Personal Folders\Lewis\Quad C Stewardship\Pictures\MarkPastorius.jpg"
Master.Tables(1).Rows(2).Cells(3).Range.FormattedText.Copy
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(2).Range.PasteAndFormat (wdFormatOriginalFormatting)
End If

If temp1 = "Toni McClure" Then
Documents(1).Tables(1).Rows(i).Cells(2).Range.Text = Master.Tables(1).Rows(3).Cells(2).Range
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range.Collapse wdCollapseStart
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(1).Range _
.InlineShapes.AddPicture FileName:="S:\PRODUCTION RESOURCE GROUP\Personal Folders\Lewis\Quad C Stewardship\Pictures\Toni McClure.jpg"
Master.Tables(1).Rows(3).Cells(3).Range.FormattedText.Copy
Documents(1).Tables(1).Rows(arrfinal(i)).Cells(2).Range.PasteAndFormat (wdFormatOriginalFormatting)

End If
Next i
End If
Next
End Sub


Any help you can provide is greatly appreciated. Thank you!!!
-Kyle

[macropod]

Seems to me you're complicating something that could be done rather easily via a mailmerge ... unless of course there's a particular reason for the user-defined sort order. Even then, you could add the sort #s to the data table (using a column set aside for the purpose), then sort the table by that column, then run the mailmerge for only the entries with a value in the sort column (controllable via a SKIPIF field in the mailmerge main document). Simply clear the sort column, then re-sort the table when you're done.

[lewiskyler]

Thanks for the reply macropod...

That's not a bad idea to add an "order" textbox for each combobox. If I did it that way I could use checkboxes instead of comboboxes, right?

If I took that approach I could set the row number = "order field" integer correct? Would I just do that like:

Documents(1).Tables(1).Rows(variable).Range.Text = Master.Tables(1).Rows(2).Cells(2).Range


Thanks!

[macropod]

How would you propose to have 100 checkboxes, and determine what values go with them? Checkboxes, after all, only have two states - checked/unchecked. Furthermore, if you had:

an "order" textbox for each combobox
why would you need the checkboxes? Surely the absence of a value is equivalent to not checked; anything else is equivalent to checked!

What you could do is have one box listing all the possibilities and another, into which they can be copied. See the Style Copier app here for some ideas:
http://www.msofficeforums.com/word-vba/10450-loop-through-listboxes-application-organizercopy-3.html#post28497
(you may have to register to access it).