VBA Help: Transferring Userform Data to Word

[pr1nyc1]

Hello All,

I have an Excel template where we house project timelines (dates started, dates ended, etc). I need to get those dates into Word at particular bookmarks. I am not sure what the most efficient way to do this is, but I started out by creating a UserForm where the user inputs the unique project number, then the Userform Vlookups the dates and inputs them into the different textboxes. I have 9 textboxes in my UserForm that are inputted with dates that need to be transferred to Word. The code below shows the how the UserForm Vlookups the date from the spreadsheet and inserts into a particular textbox.

Private Sub AuditNumber_AfterUpdate() 

'check to see if value exists
If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.AuditNumber) = 0 Then
    MsgBox "Audit Number Doesn't Exist"
    Me.AuditNumber.Value = ""
    Exit Sub
End If 

With Me
    .AnnouncementMemo = Application.WorksheetFunction.VLookup((Me.AuditNumber), Sheet1.Range("A:Z"), 6, 0)
    .AnnouncementMemo = Format(AnnouncementMemo.Value, "mm/dd/yy")
End With 

End Sub

Once the textbox in the Userform get filled out, I need to get the values from the textboxes into their particular bookmark within the Word template. Below is the code I am using once the user clicks on the "Run" command button in the Userform. The code opens up the correct template and locates the correct bookmark, but instead of putting in what was populated in the Userform, it will insert the characters in the quotations (in the below example: Me.AnnouncementMemo.Value).

Private Sub cmdRun_Click()

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

'Set wrdDoc = wrdApp.Documents.Add
Set wrdDoc = wrdApp.Documents.Open("S:\NJ\InternalAudit\RCM Macro project\JL Test - CSD Standards Phase III Planning Cover Sheet.docx")

With wrdDoc
    .Application.Selection.GoTo What:=wdGoToBookmark, Name:="AnnouncementMemoBM"
    'insert date from UserForm Textbox
    .Application.Selection.InsertAfter "Me.AnnouncementMemo.Value"
End With 

End Sub

Am i missing to Set an object? I am not sure how to proceed or if what I'm trying to do is feasible. Can someone assist? Thank you very much and let me know if anything needs to be clarrified.

 

[Macropod]

Try:

With wrdDoc
    'insert date from UserForm Textbox
    .Bookmarks("AnnouncementMemoBM").Range.Text = Me.AnnouncementMemo.Value
End With

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.