#1
|
|||
|
|||
Excel Userform to Word Template Bookmarks
Hi,
I'm new to the forum so If i haven't given enoguh info/you need anything inparticular I can easily sort! Apologies for missing it out in advance!! The Issue I have made an excel spreadsheet with a command button which when pressed automatically opens a excel userform. This userform then opens up my word template (with bookmarks). The userform will automatically fill it's fields using particular cell refrences and a bit of manual entry and then when I press okay I want the inputs to then populate the word bookmarks. The issue is that when I press okay I get an error 424. Because excel is trying to fill in it's own bookmarks not MSwords. So my question is...is this possible? Can I populate the bookmarks in word using a userform from excel? My userform code below: Code:
Private Sub UserForm_Initialize() Dim wApp As Object Dim wDoc As Object Set wApp = CreateObject("Word.Application") wApp.Visible = True wApp.DisplayAlerts = False Set wDoc = wApp.Documents.Open(Filename:="C:\Documents and Settings\jcrinage\Application Data\Microsoft\Templates\BradfordFactorIssueVW.dot ", ReadOnly:=False) With cboWarningDuration .AddItem "3 months" .AddItem "6 months" .AddItem "9 months" .AddItem "12 months" End With With cboAppealChairman .AddItem "Person 1" .AddItem "Person 2" .AddItem "Person 3" .AddItem "Person 4" .AddItem "Person 5" End With cboWarningDuration.Value = " 6 months" End Sub Private Sub txtRecipientAddress_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Automatic fill-in for salutation control. On Error Resume Next Me.txtSalutation = Left(txtRecipientAddress, InStr(Me.txtRecipientAddress, vbCrLf) - 1) On Error GoTo 0 End Sub Private Sub cmdClear_Click() txtRecipientAddress.Value = Null txtSalutation.Value = Null txtCurrentBFScore.Value = Null cboAppealChairman.Value = Null cboWarningDuration.Value = Null txtHearingDate.Value = Null txtAdditionalComments = Null End Sub Private Sub cmdCancel_Click() Unload Me ActiveDocument.Close SaveChanges:=False End Sub Private Sub cmdOK_Click() Dim strSenderName As String Dim strMeetingContact As String Dim strTriggerPoint As String Dim strWarningType As String Dim strFinalWording As String If optTriggerPoint1 = True Then strTriggerPoint = "first trigger point (>50 Points)" If optTriggerPoint2 = True Then strTriggerPoint = "second trigger point (>201 Points)" If optTriggerPoint3 = True Then strTriggerPoint = "third trigger point (>301 Points)" If optTriggerPoint4 = True Then strTriggerPoint = "final trigger point ( >501 Points)" If optTriggerPoint1 = True Then strWarningType = "verbal warning" End If If optTriggerPoint2 = True Then strWarningType = "first written warning" End If If optTriggerPoint3 = True Then strWarningType = "final written warning" End If If optTriggerPoint4 = True Then strWarningType = "dismissal notice" End If If optTriggerPoint1 = True Then strFinalWording = "first trigger point on the Bradford Factor calculation further disciplinary action may result once a score of 201 or above is reached" End If If optTriggerPoint2 = True Then strFinalWording = "second trigger point on the Bradford Factor calculation further disciplinary action may result once a score of 301 or above is reached" End If If optTriggerPoint3 = True Then strFinalWording = "third trigger point on the Bradford Factor calculation further disciplinary action may result once a score of 501 or above is reached" End If If optTriggerPoint4 = True Then strFinalWording = "final trigger point on the Bradford Factor calculation discplinary proceedings have been put in place" End If If cboAppealChairman.Value = "Person 1" Then strSenderName = "Person 1" & vbCrLf & "Shareholder" End If If cboAppealChairman.Value = "Person 2" Then strSenderName = "Person 2" & vbCrLf & "Managing Director" End If If cboAppealChairman.Value = "Person 3" Then strSenderName = "Person 3" & vbCrLf & "Installations Manager" End If If cboAppealChairman.Value = "Person 4" Then strSenderName = "Person 4" & vbCrLf & "Financial Director" End If If cboAppealChairman.Value = "Person 5" Then strSenderName = "Person 5" & vbCrLf & "Factory Manager" End If Application.ScreenUpdating = False With wDoc .Bookmarks("RecipientName").Range.Text = txtRecipientAddress.Value .Bookmarks("Salutation").Range.Text = txtSalutation.Value .Bookmarks("MeetingDate").Range.Text = txtHearingDate.Value .Bookmarks("TriggerPoint").Range.Text = strTriggerPoint .Bookmarks("CurrentBFScore").Range.Text = txtCurrentBFScore.Value .Bookmarks("DisciplinaryIssued").Range.Text = strWarningType .Bookmarks("DisciplinaryIssued2").Range.Text = strWarningType .Bookmarks("DisciplinaryPeriod").Range.Text = cboWarningDuration.Value .Bookmarks("FinalWording").Range.Text = strFinalWording .Bookmarks("Appeal").Range.Text = cboAppealChairman.Value .Bookmarks("Signature").Range.Text = strSenderName .Bookmarks("AdditionalComments").Range.Text = txtAdditionalComments.Value End With Application.ScreenUpdating = True Unload Me End Sub Last edited by macropod; 11-02-2016 at 06:54 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
The code you've posted isn't trying to update Excel at all, and your Word bookmark population process isn't responsible for any errors.
Unless you're planning to edit the template, instead of creating a new document from it, you should be using Word's Documents.Add method, not Documents.Open. PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Use Bookmarks to Fill in a Word Doc from Excel | MH123 | Excel Programming | 0 | 10-14-2016 08:39 AM |
Cross Referencing Bookmarks populated from a userform Word 2010 | arbluecreek | Word VBA | 6 | 06-05-2015 05:29 AM |
Transfer word doc bookmarks to excel using macro | Bedsy | Word VBA | 4 | 03-15-2015 10:58 PM |
Update Bookmarks from a Userform | alshcover | Word VBA | 12 | 01-12-2015 06:53 PM |
Trying to populate Word bookmarks with excel UserForm | smd1112 | Excel Programming | 7 | 09-03-2014 09:42 PM |