#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
How is the document formatted, with relation to the bookmarks it contains? Presumably from the description you are adding to some sort of list in the document. Bookmarks may not be the best way to do that.
Can you make a copy of the document available,so that we can see what we are dealing with? The forum allows attachments.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Yes. Thank you for taking a look at it. I am open to any suggestions.
|
#4
|
||||
|
||||
There are several issues. 1. If others are going to use this document, ensure that you change the font on your userform to a standard font, for if the font is not available (it's not here) the display is truly dreadful. I have made some changes
2. The issue with populating the bookmarks is frankly never going to work as you have it. Better to assemble the values from the userform and write them all to the end of the document - addressed! 3. Check boxes make more sense than list boxes for yes no choices. 4. The code for opening the document needed some work. 5. And most important of all, if you want to repeat the data, you can't run a command from the form if you have already unloaded the form. Much better to run the code outside the form as shown in the attached. I think that should get you started
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Thank you so much! I really appreciate it. This one is much better than mine. But, when I click submit, the word document opens but then disappears and I get a debug message. Any thoughts?
|
#6
|
||||
|
||||
The document is supposed to disappear. What does the message say, and if you click Debug, what line of code is highlighted?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
The error message says:
Run-time error '-2147418105 (80010007): The callee (server [not server application]) is not available and disappeared; all corrections are invalid. The call may have executed. When I run the debug, the following line of code is highlighted: If .Tag = 0 Then GoTo lbl_Exit: |
#8
|
||||
|
||||
This error occurs when the userform has been unloaded before the rest of the code can complete. My guess is that you clicked the cross in the top right of the userform to close the form. To overcome this add the following to the userform code
Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then CommandButtonCancel_Click Cancel = True End If lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross Referencing Bookmarks populated from a userform Word 2010 | arbluecreek | Word VBA | 6 | 06-05-2015 05:29 AM |
Update Bookmarks from a Userform | alshcover | Word VBA | 12 | 01-12-2015 06:53 PM |
send a string from Excel UserForm to Word | saltlakebuffalo | Excel Programming | 1 | 02-10-2014 11:01 PM |
Auto Populate Word From to Excel File | webber | Word | 1 | 10-02-2013 02:52 PM |
How do I use an Excel form to populate and Excel spreadsheet | apostht | Excel | 0 | 05-21-2006 11:17 AM |