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!
|