none
VBA Word For Each Loop through Comboboxes to Populate Word Table RRS feed

  • General discussion

  • 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

    Wednesday, October 3, 2012 7:39 PM

All replies

  • My first tought is that Word is not the best application to do that, but Excel for two reasons:

    -Your code relies heavily in tables. The Excel object model works with tables better than Word.

    -You might use an auxiliar table to create a relationship between people's names and path of where the image of each one is.

    Why don't create a master table in Excel, run a macro to populate it and then paste in an Word document?

    However, I couldn't figure out how the tables and userforms will bem. Could you upload a document (or image) of the "master" document, the userform that has controls and an example of a correct output table?

    *use SkyDrive to upload files, for example.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thursday, October 4, 2012 12:25 AM