VBA for MS word document RRS feed

  • Question

  • So what I need it to do is it pops up with a display. User inputs information, has a few drop downs, and the letter will write itself dependant upon what is selected.

    I have the beginning of the letter done but now comes the tricky part.

    There are 6 sections of the letter, the verbage breaks each section into 2 (one paragraph is for multiple entries, one paragraph is for single entries, due to verbage)

    There is a dragdown on the initial popup to select multiple or single entires and i'm not sure where to go from here.

    I'm attaching what i can.

    Private Sub Cancel_Click()
        Unload Me
        ActiveDocument.Close SaveChanges:=False
    End Sub
    Private Sub OK_Click()
        With ActiveDocument
            .Bookmarks("CompanyName").Range.Text = txtCompanyName.Value
            .Bookmarks("Attn1").Range.Text = txtAttn1.Value
            .Bookmarks("Attn2").Range.Text = txtAttn2.Value
            .Bookmarks("Attn3").Range.Text = txtAttn1.Value
            .Bookmarks("Address1").Range.Text = txtAddress1.Value
            .Bookmarks("Address2").Range.Text = txtAddress2.Value
            .Bookmarks("Phone").Range.Text = txtPhone.Value
            .Bookmarks("Fax").Range.Text = txtFax.Value
            .Bookmarks("ProjectNumber").Range.Text = txtProjectNumber.Value
            .Bookmarks("ProjectName").Range.Text = txtProjectName.Value
            .Bookmarks("BuildingSection").Range.Text = txtBuildingSection.Value
            .Bookmarks("ProjectName2").Range.Text = txtProjectName.Value
            .Bookmarks("City").Range.Text = txtCity.Value
            .Bookmarks("State").Range.Text = txtState.Value
        End With
        Application.ScreenUpdating = True
        Unload Me
    End Sub
    Private Sub UserForm_Initialize()
        With cboType
            .AddItem "Slab on Grade"
            .AddItem "Elevated"
        End With
        With cboBroken
            .AddItem "Multiple"
            .AddItem "One"
        End With
        With cboMissing
            .AddItem "Multiple"
            .AddItem "One"
        End With
        With cboSlightlyBelow
            .AddItem "Multiple"
            .AddItem "One"
        End With
        With cboWellBelow
            .AddItem "Multiple"
            .AddItem "One"
        End With
        With cboSlightlyAbove
            .AddItem "Multiple"
            .AddItem "One"
        End With
        With cboWellAbove
            .AddItem "Multiple"
            .AddItem "One"
        End With
    End Sub

    If you want to email me i can send you the file. I'm sure it's simple i just don't knwo that much about coding. I'll get it after i see something similar as i understand when reading a line of cod as to what does what, but my search on the net has been fruitless

    Monday, August 12, 2013 11:54 PM

All replies

  • This is all very straightforward and the basics are covered at 

    If you need to get more adventurous then see 

    If you want to insert longer texts based on the value of a dropdown then you can do that by inserting building blocks, documents or even hard code the text in the macro.

    I would suggest that you re-assign the bookmarks after writing to them. That way you can recall the userform to make changes.  You can do that with a simple function (below) and call it from your OK button e.g.

    Private Sub OK_Click() With ActiveDocument FillBM "CompanyName", txtCompanyName.Text FillBM "Attn1", txtAttn1.Text FillBM "Attn2", txtAttn2.Text


    Function FillBM(ByVal strBookmark As String, _
                    ByVal strText As String) As Boolean
    Dim oBookmark As Bookmark
    Dim oRng As Word.Range
    Dim oDoc As Document
        Set oDoc = ActiveDocument
        If oDoc.Bookmarks.Exists(strBookmark) Then
            Set oBookmark = oDoc.Bookmarks(strBookmark)
            Set oRng = oBookmark.Range
            If Len(strText) > 0 Then
                oRng.Text = strText
                oDoc.Bookmarks.Add strBookmark, oRng
            End If
            FillBM = True
        End If
        Set oDoc = Nothing
        Set oRng = Nothing
        Set oBookmark = Nothing
    End Function

    Graham Mayor - Word MVP

    Wednesday, August 14, 2013 1:14 PM