Fill Word table using data from UserForm

[Rottimom]

I am extremely new to VBA but need to create a log file in Word that has a table that is filled out. While I know it would be better to do this in Excel that is not an option. What I want to do is prompt the users for 9 fields of data that is populated to the corresponding fields on the table. Then when they click a button to enter another record at which point the UserForm is cleared and represented for the second set of data to be entered. This continues until the user clicks the finish button at which point the Word document is presented to the user.

I have a mainFrm that collects information that is placed above the table such as persons name. I then created UserForm1 with the 9 fields for the data but can not manage to unload the first form and get the second form to show. Even after I get that to work I need to know how to unload the data from UserForm1 to one row of the table and move to the next row.

Any help is greatly appreciated.

[fumei]

but can not manage to unload the first form and get the second form to show.
Why would you want to unload the form (first). Use the SAME userform, not multiple ones.

More deatils are needed. You state that data from the userform is going to one row. Does that mean there are nine columns? Multiple data points in one cell? Will the location to place the data be at bookmarks?

[Rottimom]

Why would you want to unload the form (first). Use the SAME userform, not multiple ones.

More deatils are needed. You state that data from the userform is going to one row. Does that mean there are nine columns? Multiple data points in one cell? Will the location to place the data be at bookmarks?

First THANKS so much for the reply. The mainfrm form prompts for employee names the second will loop to ask for the information that is placed in the table. What we are trying to do is create a log of checks that we receive. So each row in the table represents one check. The columns are first line number of the row (1, 2, 3 etc) then company name on the check, amount of check, what the check is for, number on the check, blah, blah. After entering each check the user clicks a button that indicates if they have another check to enter or if they are done and want the document to be created.

so the final document would look something like this:

Name of person creating log

(In a table)
Line number Company Name Amount of check What check is for Check #
1 ABC Company $1000 Fee payment 12345
2 NBC Company $20 Registration fee 234
3 ZZZ Company $10 Misc fee 33333

Once again THANK YOU so much for your help!

[gmaxey]

There is an example of this process here: http://gregmaxey.mvps.org/word_tip_pages/repeating_item_lists.html

[Rottimom]

Got most of the form created. What I still need to do is total the "Amount" column and number each row that is added sequentially (1, 2, 3)

Thanks for all the help it is greatly appreciated.

PS Greg, I love the your site and a donation is on its way shortly.

[gmaxey]

Would need to see your document\code. It sounds like your table will need 7 columns. In the first column add a SEQ field. This will sequentially number the rows. Then you will need to add a final row and in column 7 add a formula { = Sum(Above)} field.

[Rottimom]

I am using your code.

With oTbl
.Style = "Table Grid"
With .Rows(1)
.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
End With
.Cell(1, 1).Range.Text = "#"
.Cell(1, 2).Range.Text = "Case Number"
.Cell(1, 3).Range.Text = "Payor"
.Cell(1, 4).Range.Text = "(FBO)"
.Cell(1, 5).Range.Text = "Ref #"
.Cell(1, 6).Range.Text = "Purpose"
.Cell(1, 7).Range.Text = "Check/M.O.#"
.Cell(1, 8).Range.Text = "Amount"
.Cell(1, 9).Range.Text = "Receipt #"

'Add data to table.
On Error GoTo Err_NoRecords
For lngIndex = 1 To oCol.Count
Set oObjForm = oCol.Item(lngIndex)
.Cell(lngIndex + 1, 2).Range.Text = oObjForm.txtCSNum.Text
.Cell(lngIndex + 1, 3).Range.Text = oObjForm.txtPayor.Text
.Cell(lngIndex + 1, 4).Range.Text = oObjForm.txtFBO.Text
.Cell(lngIndex + 1, 5).Range.Text = oObjForm.txtRefNum.Text
.Cell(lngIndex + 1, 6).Range.Text = oObjForm.txtPurpose.Text
.Cell(lngIndex + 1, 7).Range.Text = oObjForm.txtChkNum.Text
.Cell(lngIndex + 1, 8).Range.Text = oObjForm.txtAmnt.Text
.Cell(lngIndex + 1, 9).Range.Text = oObjForm.txtRcpt.Text
.Columns.AutoFit

Set oObjForm = Nothing
Next lngIndex
End With

I need column 1 to have the sequential numbering. Then once all checks are entered I need column 8 (Amount Field) totaled. The total is being added above the table to a total line. I would be happy to send you a copy of my document if that would help.

[gmaxey]

You need to add a sequence field to column 1 then add a final row with a formula field in column 8:


Dim oRng as Word.Rng
oTbl
.Style = "Table Grid"
.Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Cell(1, 1).Range.Text = "#"
.Cell(1, 2).Range.Text = "Case Number"
.Cell(1, 3).Range.Text = "Payor"
.Cell(1, 4).Range.Text = "(FBO)"
.Cell(1, 5).Range.Text = "Ref #"
.Cell(1, 6).Range.Text = "Purpose"
.Cell(1, 7).Range.Text = "Check/M.O.#"
.Cell(1, 8).Range.Text = "Amount"
.Cell(1, 9).Range.Text = "Receipt #"

'Add data to table.
On Error Goto Err_NoRecords
For lngIndex = 1 To oCol.Count
Set oObjForm = oCol.Item(lngIndex)
Set oRng = .Cell(lngIndex + 1, 1).Range
oRng.Collapse wdCollapseStart
ActiveDocument.Fields.Add oRng, wdFieldSequence, "Number"
.Cell(lngIndex + 1, 2).Range.Text = oObjForm.txtCSNum.Text
.Cell(lngIndex + 1, 3).Range.Text = oObjForm.txtPayor.Text
.Cell(lngIndex + 1, 4).Range.Text = oObjForm.txtFBO.Text
.Cell(lngIndex + 1, 5).Range.Text = oObjForm.txtRefNum.Text
.Cell(lngIndex + 1, 6).Range.Text = oObjForm.txtPurpose.Text
.Cell(lngIndex + 1, 7).Range.Text = oObjForm.txtChkNum.Text
.Cell(lngIndex + 1, 8).Range.Text = oObjForm.txtAmnt.Text
.Cell(lngIndex + 1, 9).Range.Text = oObjForm.txtRcpt.Text
.Columns.AutoFit

Set oObjForm = Nothing
Next lngIndex
.Rows.Add
Set oRng = .Cell(oTbl.Rows.Count, 8).Range
oRng.Collapse wdCollapseStart
ActiveDocument.Fields.Add oRng, wdFieldEmpty, "= Sum(Above)"
End With