Userforms and VBA in Word 2007/2010 - Combobox with addresses

Anonymous
2012-02-09T23:44:33+00:00

Hello,

I've been trying to get used to working with User forms, and I've gone as far as being able to create a form, but I am uncertain how to code it to work with a template.  I'm still waiting for some books to come in that I ordered to help me out, so in the mean time I'm looking for a little assistance from you amazing people :-)

The main thing I'm trying to work on is creating a combobox so that I can have a very long list that will continue to grow as I obtain more contacts.  I wanted to use dropdown form fields but realize I can't exceed 25 items per dropdown field.  I could use several dropdown fields to call the data from another document, but I'd like things to look clean.  So I think a combobox will be a much better option.  I want the combo box to have the names of all my contacts, and once a contact is selected, I want their entire mailing address to appear at the top of my letter template.

So let's say I select 'Option1' in the combo box, I'd like the top of my letter to show something resembling the following:

Recipient Name

Address

City, State/Province

Zip Code/Postal Code

I hope I've explained what I'd like done clearly enough, but if you require further explanation I'd be happy to provide.  If there's a better way to do this I'd be happy to hear it.

Thank you!

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-02-10T07:04:41+00:00

The simplest solution, if you have it, is to use Outlook to both store the addresses and provide an address book dialog to insert the addresses where you require them. This is covered at http://www.gmayor.com/Macrobutton.htm.

If you want to list the addresses on a userform you will need a multi-column list box and somewhere to store the addresses - probably a document containing a table or perhaps an Excel worksheet, which can be read into the list box. Take a look at http://gregmaxey.mvps.org/word_tip_pages/Old_Tip_Pages/Populate_UserForm_ListBox.htm.

You can then populate a document variable with the contents of the selected item and use a docvariable field to reproduce the value from the docvariable in the document.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2012-02-16T08:44:29+00:00

Don't use bookmarks with a protected form, use a docvariable field in the document and write the value of the selected item from the list box to the variable. Then the protection becomes irrelevant.

If using a multi-column list box, then you will need to re compile the address from the list box columns in the format you require into the variable. Something like the following. I have set the listbox to display only the first column to avoid clutter.

Insert { DocVariable Address } into the document at the place you want the address to appear.

Option Explicit

Private oVars As Variables

Private oSource As Document

Private oTable As Table

Private oRng As Range

Private strWidth As String

Private i As Long, j As Long, k As Long

Private m As Long, n As Long

Private Sub UserForm_Initialize()

Set oSource = Documents.Open(FileName:="C:\Addresses.doc", _

                             AddToRecentFiles:=False, _

                             Visible:=False)

Set oTable = oSource.Tables(1)

'Get the number of list members (i.e., table rows - 1 if header row is used)

i = oTable.Rows.Count - 1

'Get the number of list member attributes (i.e., table columns)

j = oTable.Columns.Count

'Set the number of columns in the Listbox

With Me.ListBox1

    .ColumnCount = j

    strWidth = .Width - 2

    For k = 2 To j

        strWidth = strWidth & ";" & 0

    Next k

    .ColumnWidths = strWidth

    'Load list members into an array

    ReDim myArray(i, j)

    For n = 0 To j - 1

        For m = 0 To i - 1

            Set oRng = oTable.Cell(m + 2, n + 1).Range

            oRng.End = oRng.End - 1

            myArray(m, n) = oRng.Text

        Next m

    Next n

    'Populate the ListBox using the array

    .list() = myArray

    .ListIndex = 0

End With

'Close the source file

oSource.Close SaveChanges:=wdDoNotSaveChanges

End Sub

Private Sub CommandButton1_Click()

Set oVars = ActiveDocument.Variables

oVars("Address").Value = Me.ListBox1.Column(0) & vbCr & _

                         Me.ListBox1.Column(1) & vbCr & _

                         Me.ListBox1.Column(2) & vbCr & _

                         Me.ListBox1.Column(3)

ActiveDocument.Fields.Update

Unload Me

End Sub

Was this answer helpful?

0 comments

42 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Doug Robbins - MVP - Office Apps and Services 323.6K Reputation points MVP Volunteer Moderator
    2012-03-10T00:31:35+00:00

    Assuming that the data starts in Cell A1, try
        cRows = xlWS.Range("A1").CurrentRegion.Rows.Count
       cCols = xlWS.Range("A1").CurrentRegion.Columns.Count

    Instead of
        cRows = xlWS.Range("A65536").End(xlUp).Row
       cCols = xlWS.Cells(1, xlWS.Columns.Count).End(xlToLeft).Column

    Hope this helps.

    Doug Robbins - Word MVP,
    Email: dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LAssist2011" wrote in message news:******@communitybridge.codeplex.com.word...

    I get a compile error "Variable not defined" at this line:

    cRows = xlWS.Range("A65536").End(xlUp).Row

    Here's what I have:

    Private Sub Userform_Initialize()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim cRows As Long
    Dim cCols As Long
    Dim i As Long
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Templates\TemplateDocs\ClientInfo.xlsx")
    Set xlWS = xlWB.Worksheets(1)

    cRows = xlWS.Range("A65536").End(xlUp).Row
    cCols = xlWS.Cells(1, xlWS.Columns.Count).End(xlToLeft).Column

    ListBox2.ColumnCount = cCols
    With Me.ListBox2
        .ColumnCount = cCols
        strWidth = .Width - 2
        For k = 2 To cCols
            strWidth = strWidth & ";" & 0
        Next k
        .ColumnWidths = strWidth
        For i = 2 To cRows
            .AddItem xlWS.Cells(i, 1)
            For j = 1 To cCols
                .List(.ListCount - 1, j) = xlWS.Cells(i, j + 1)
            Next j
        Next i
    End With
    Set xlWS = Nothing
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    Set oSource1 = Documents.Open(FileName:="C:\Templates\TemplateDocs\Addresses.docx", _
                                  AddToRecentFiles:=False, _
                                  Visible:=False)
    Set oTable = oSource1.Tables(1)
     'Get the number of list members (i.e., table rows - 1 if header row is used)
     i = oTable.Rows.Count - 1
     'Get the number of list member attributes (i.e., table columns)
     j = oTable.Columns.Count
     'Set the number of columns in the Listbox
     With Me.ListBox1
          .ColumnCount = j
         strWidth = .Width - 2
         For k = 2 To j
             strWidth = strWidth & ";" & 0
         Next k
         .ColumnWidths = strWidth
         'Load list members into an array
         ReDim myArray(i, j)
         For n = 0 To j - 1
             For m = 0 To i - 1
                 Set oRng = oTable.Cell(m + 2, n + 1).Range
                 oRng.End = oRng.End - 1
                 myArray(m, n) = oRng.Text
             Next m
         Next n
         'Populate the ListBox using the array
         .List() = myArray
         .ListIndex = 0
     End With
     oSource1.Close SaveChanges:=wdDoNotSaveChanges
    End Sub

    http://answers.microsoft.com/message/ec471d11-75f2-4264-ab27-cedf162955a6?threadId=9101a17a-9f74-4265-962e-fdd0eb6bfb56
    Meta tags: windows_7; word; office_2007

    Fri, 9 Mar 2012 18:40:17  +0000: CreateMessage  LAssist2011

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-03-10T08:59:30+00:00

    Doug's suggestion should work also, but I am unsure why the original produced an error. It works fine here.

    I should also point out that I had not checked that I had defined all the variables used in this segment when I copied it to the message format.

    Dim j As Long

    Dim k As Long

    Dim strWidth As String

    should be added. It is good practice to declare all variables.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-03-12T13:41:35+00:00

    Now I receive "Could Not Set the List Property, Invalid Property Value".

    I'll continue playing with what you provided to me to see if I can't figure this out on my own, but in the mean time here's my code in its entirety and not just my intialize event:

    Option Explicit

    Private oVars As Variables

    Private oSource1 As Document

    Private oTable As Table

    Private oRng As Range

    Private strWidth As String

    Private i As Long, j As Long, k As Long

    Private m As Long, n As Long

    Private Sub CmdBtnCancel_Click()

        Unload Me

    End Sub

    Private Sub CommandButton1_Click()

    With ActiveDocument

        .Variables("Salutation").Value = Me.TextBox1.Text

        .Variables("Attention").Value = Me.TextBox2.Text

        .Variables("SentBy").Value = Me.TextBox3.Text

        .Variables("Period").Value = Me.TextBox4.Text

        .Variables("FileUnder").Value = Me.TextBox5.Text

        .Variables("Amount").Value = Me.TextBox6.Text

        .Fields.Update

      End With

     Set oVars = ActiveDocument.Variables

     oVars("Address").Value = Me.ListBox1.Column(1) & vbCr & _

                              Me.ListBox1.Column(2) & vbCr & _

                              Me.ListBox1.Column(3) & vbCr & _

                              Me.ListBox1.Column(4)

     oVars("1").Value = Me.ListBox2.Column(0)

     oVars("2").Value = Me.ListBox2.Column(1)

     oVars("3").Value = Me.ListBox2.Column(2)

     oVars("4").Value = Me.ListBox2.Column(3)

     oVars("5").Value = Me.ListBox2.Column(4)

     oVars("6").Value = Me.ListBox2.Column(5)

     oVars("7").Value = Me.ListBox2.Column(6)

     oVars("8").Value = Me.ListBox2.Column(7)

     oVars("9").Value = Me.ListBox2.Column(8)

     oVars("10").Value = Me.ListBox2.Column(9)

     oVars("11").Value = Me.ListBox2.Column(10)

     oVars("12").Value = Me.ListBox2.Column(11)

     oVars("13").Value = Me.ListBox2.Column(12)

     oVars("14").Value = Me.ListBox2.Column(13)

     oVars("15").Value = Me.ListBox2.Column(14)

     oVars("16").Value = Me.ListBox2.Column(15)

     oVars("17").Value = Me.ListBox2.Column(16)

     oVars("18").Value = Me.ListBox2.Column(17)

     oVars("19").Value = Me.ListBox2.Column(18)

     oVars("20").Value = Me.ListBox2.Column(19)

    WriteTextToBM "Closing", Me.ListBox3.Column(1)

    WriteTextToBM "Name", Me.ListBox3.Column(0)

    WriteTextToBM "Position", Me.ListBox3.Column(3)

    InsertBBInBM "SigImage", Me.ListBox3.Column(2)

     ActiveDocument.Fields.Update

     Unload Me

    End Sub

    Private Sub Userform_Initialize()

    Dim xlApp As Object

    Dim xlWB As Object

    Dim xlWS As Object

    Dim cRows As Long

    Dim cCols As Long

    Dim i As Long

    Dim j As Long

    Dim k As Long

    Dim strWidth As String

    Set xlApp = CreateObject("Excel.Application")

    Set xlWB = xlApp.Workbooks.Open("C:\Templates\TemplateDocs\ClientInfo.xlsx")

    Set xlWS = xlWB.Worksheets(1)

    Set oSource1 = Documents.Open(FileName:="C:\Templates\TemplateDocs\Addresses.docx", _

                                  AddToRecentFiles:=False, _

                                  Visible:=False)

    Set oTable = oSource1.Tables(1)

     'Get the number of list members (i.e., table rows - 1 if header row is used)

     i = oTable.Rows.Count - 1

     'Get the number of list member attributes (i.e., table columns)

     j = oTable.Columns.Count

     'Set the number of columns in the Listbox

     With Me.ListBox1

          .ColumnCount = j

         strWidth = .Width - 2

         For k = 2 To j

             strWidth = strWidth & ";" & 0

         Next k

         .ColumnWidths = strWidth

         'Load list members into an array

         ReDim myArray(i, j)

         For n = 0 To j - 1

             For m = 0 To i - 1

                 Set oRng = oTable.Cell(m + 2, n + 1).Range

                 oRng.End = oRng.End - 1

                 myArray(m, n) = oRng.Text

             Next m

         Next n

         'Populate the ListBox using the array

         .List() = myArray

         .ListIndex = 0

     End With

     oSource1.Close SaveChanges:=wdDoNotSaveChanges

    ListBox2.ColumnCount = cCols

    With Me.ListBox2

    cRows = xlWS.Range("A1").CurrentRegion.Rows.Count

    cCols = xlWS.Range("A1").CurrentRegion.Columns.Count

        .ColumnCount = cCols

        strWidth = .Width - 2

        For k = 2 To cCols

            strWidth = strWidth & ";" & 0

        Next k

        .ColumnWidths = strWidth

        For i = 2 To cRows

            .AddItem xlWS.Cells(i, 1)

            For j = 1 To cCols

                .List(.ListCount - 1, j) = xlWS.Cells(i, j + 1)

            Next j

        Next i

    End With

    Set xlWS = Nothing

    Set xlWB = Nothing

    xlApp.Quit

    Set xlApp = Nothing

    With Me.ListBox3

       .ColumnCount = 4

       .ColumnWidths = "150;0;0;0"

       .AddItem "NAME1"

       .List(0, 1) = "Yours truly,"

       .List(0, 2) = "bbNAME1"

       .List(0, 3) = "Job Title"

       .AddItem "NAME2"

       .List(1, 1) = "Yours truly,"

       .List(1, 2) = "bbNAME2"

       .List(1, 3) = "Job Title"

       .AddItem "NAME3"

       .List(2, 1) = "Yours truly,"

       .List(2, 2) = "bbNAME3"

       .List(2, 3) = "Job Title"

       .AddItem "NAME4"

       .List(3, 1) = "Yours truly,"

       .List(3, 2) = "bbNAME4"

       .List(3, 3) = "Job Title"

       .AddItem "NAME5"

       .List(4, 1) = "Yours truly,"

       .List(4, 2) = "bbNAME5"

       .List(4, 3) = "Job Title"

    End With

    End Sub

    Sub WriteTextToBM(ByRef strName As String, strText As String)

    Dim oRng As Word.Range

    Set oRng = ActiveDocument.Bookmarks(strName).Range

    oRng.Text = strText

    ActiveDocument.Bookmarks.Add strName, oRng

    End Sub

    Sub InsertBBInBM(ByRef strName As String, strBBName As String)

    Dim oRng As Word.Range

    Set oRng = ActiveDocument.Bookmarks("SigImage").Range

    Set oRng = ActiveDocument.AttachedTemplate.BuildingBlockTypes(wdTypeAutoText).Categories _

               ("General").BuildingBlocks(strBBName).Insert(Where:=oRng, RichText:=True)

    ActiveDocument.Bookmarks.Add "SigImage", oRng

    End Sub

    Was this answer helpful?

    0 comments