Microsoft Office Forums - View Single Post

gmayor · 04-08-2015, 10:23 PM

The code and the userform should be stored in the document template project and not the normal template. The document template should be saved as macro enabled (DOTM) format. Create a new document from the template to run the macros.

You only need 2 buttons on the form - one to cancel, the other to perform the process(es).

The code associated with the form is

Code:

Option Explicit

Private Sub btnCancel_Click()
    With Me
        .Tag = 0
        .Hide
    End With
End Sub

Private Sub btnOK_Click()
    With Me
        .Tag = 1
        .Hide
    End With
End Sub

The processing is best done in the macro that calls the form, so in a standard module
http://www.gmayor.com/installing_macro.htm use something like the following, which includes two standard functions to fill bookmarks and write the log. Substitute (and/or add) the bookmark names, text field names and log file path as appropriate.

Insert the bookmarks where you require the data to be printed. You don't have to inbsert all the data in the document or in the log file.

Code:

Option Explicit

Sub AutoNew() 'Run when a new document is created from the template
    Call RunMyForm
lbl_Exit:
    Exit Sub
End Sub

Sub RunMyForm()
Dim oFrm As New UserForm1        'UserForm1 = The name of the userform
Dim oDoc As Document
Dim strLogEntry As String
    strLogEntry = ""
    On Error GoTo lbl_Exit
    Set oDoc = ActiveDocument
    With oFrm
        .Show
        Select Case .Tag
            Case 0: GoTo lbl_Exit        'Quit
            Case 1
                'Fill the named bookmark with the value from the userform
                FillBM "BookmarkName1", .TextBox1.Text
                'repeat for each bookmark/textbox name
                FillBM "BookmarkName2", .TextBox2.Text
                'Repeat for each textbox/logged item
                strLogEntry = strLogEntry & .TextBox1.Text & ", "
                'Omit ", " from the last named item.
                strLogEntry = strLogEntry & .TextBox2.Text
                'Update the log
                UpDateLog strLogEntry
        End Select
    End With
lbl_Exit:
    Unload oFrm
    Set oFrm = Nothing
    Set oDoc = Nothing
    Exit Sub
End Sub

Private Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        Set oRng = .Bookmarks(strBMName).Range
        oRng.Text = strValue
        oRng.Bookmarks.Add strBMName
    End With
lbl_Exit:
    Exit Sub
End Sub


Private Sub UpDateLog(strMessage As String)
Const strFileName As String = "C:\PATH\Latelog.txt"
Dim FileNum As Integer
    FileNum = FreeFile
    Open strFileName For Append As #FileNum        ' creates the file if it doesn't exist
    Print #FileNum, strMessage        ' write information at the end of the text file
    Close #FileNum        ' close the file
lbl_Exit:
    Exit Sub
End Sub