Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-20-2014, 07:26 PM
smd1112 smd1112 is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2013
Novice
Trying to populate Word bookmarks with excel UserForm
 
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
  #2  
Old 08-26-2014, 04:03 AM
gmayor's Avatar
gmayor gmayor is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 08-27-2014, 02:56 PM
smd1112 smd1112 is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2013
Novice
Trying to populate Word bookmarks with excel UserForm
 
Join Date: Aug 2014
Posts: 4
smd1112 is on a distinguished road
Default

Yes. Thank you for taking a look at it. I am open to any suggestions.
Attached Files
File Type: xlsm Sample Parent Contact Log.xlsm (71.4 KB, 19 views)
Reply With Quote
  #4  
Old 08-27-2014, 10:32 PM
gmayor's Avatar
gmayor gmayor is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Attached Files
File Type: xlsm Sample Parent Contact Log.xlsm (70.6 KB, 72 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 09-02-2014, 04:49 PM
smd1112 smd1112 is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2013
Novice
Trying to populate Word bookmarks with excel UserForm
 
Join Date: Aug 2014
Posts: 4
smd1112 is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 09-02-2014, 09:38 PM
gmayor's Avatar
gmayor gmayor is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #7  
Old 09-03-2014, 03:04 PM
smd1112 smd1112 is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2013
Novice
Trying to populate Word bookmarks with excel UserForm
 
Join Date: Aug 2014
Posts: 4
smd1112 is on a distinguished road
Default

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:
Reply With Quote
  #8  
Old 09-03-2014, 09:42 PM
gmayor's Avatar
gmayor gmayor is offline Trying to populate Word bookmarks with excel UserForm Windows 7 64bit Trying to populate Word bookmarks with excel UserForm Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
Reply

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
Trying to populate Word bookmarks with excel UserForm send a string from Excel UserForm to Word saltlakebuffalo Excel Programming 1 02-10-2014 11:01 PM
Trying to populate Word bookmarks with excel UserForm 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft