View Single Post
 
Old 08-20-2014, 07:26 PM
smd1112 smd1112 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Aug 2014
Posts: 4
smd1112 is on a distinguished road
Default Trying to populate Word bookmarks with excel UserForm

Hello...
I am a teacher and am new to VBA (didn't even know what VBA was until two days ago). But I am basically trying to accomplish the following:

Create an excel file that has a UserForm that will allow me to
1) keep a log of all my communications with parents,
2) automatically pull up a Word document and input data into bookmarks ("Firstname" "Lastname" and "Reason") so I can provide students with a certificate for good behavior (the Word file is embedded in the Excel file so other teachers can use it for their classes as well).
I want all of this to happen when I click the "Submit" command button

This is the code I have so far:

Private Sub CommandButtonSubmit_Click()

'Open embedded file

Dim oleObject As Object
Dim wordDocument As Object

Set oleObject = ActiveWorkbook.Sheets("Sheet1").OLEObjects(1) 'File location

oleObject.Verb Verb:=xlPrimary
ActiveSheet.Range("A2").Select

With ActiveDocument
.Bookmarks("Firstname").Range.Text = FirstnameTextBox.Value
.Bookmarks("Lastname").Range.Text = LastnameTextBox.Value
.Bookmarks("Reason").Range.Text = ReasonTextBox.Value
End With


Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = DateTextBox.Value
Cells(emptyRow, 2).Value = LastnameTextBox.Value
Cells(emptyRow, 3).Value = FirstnameTextBox.Value
Cells(emptyRow, 4).Value = ContactmethodListBox.Value
Cells(emptyRow, 5).Value = ContactmadeListBox.Value
Cells(emptyRow, 6).Value = ReasonTextBox.Value
Cells(emptyRow, 7).Value = NotesTextBox.Value
Cells(emptyRow, 8).Value = FollowupListBox.Value

Parentcontactentry.Hide
Unload Me
Parentcontactentry.Show
End Sub

----------------------------------------------------------------
Right now when I click submit the data gets entered into my spreadsheet, the word document gets pulled up, but nothing gets entered into the bookmarks and I get a debug message. I can't figure out where I've gone wrong.

Also, I would like for the new text in the bookmarks to be replaced each time I click submit if possible.

Asking a lot, but any help would be very much appreciated.

Thanks!
Reply With Quote