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: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2012-02-16T20:45:47+00:00

    Hi Doug,

    So far my code looks like this:

    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 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

    Private Sub CmdBtnCancel_Click()

    Me.Hide

    End Sub

    Private Sub Label2_Click()

    End Sub

    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

    I just need to know what to do with my other listbox... replicate what I need to in order to get the desired effect as mentioned in my previous post.

    I forgot to add one thing.  The letters that I'm calling into my template also have reference fields, referencing the form fields on the first page of my template.  Will these ref fields call the data from the first page when the userform enters them into the document?

    My entire goal for my template is this:  I have several letters that I've been using for all of my clients where I need to send out the same letter of many types to request client information.  The first page of this template is a sort of client intake form.  This form is a simple form template with fields you can just tab through and type in or select from dropdown boxes.  When the user calls up the userform and selects the address and type of letter, I was hoping for the letter to reference some of the client information I have included in pre-made ref fields referencing the fields on the first page so that when the correct letter is selected for the template, the information for that particular client will automatically enter into the document.

    To me this sounds complicated and I hope it's possible, and I'm further hoping you understand what I'm trying to do and that you can help me again lol...

    Thanks!

    Was this answer helpful?

    0 comments
  3. Doug Robbins - MVP - Office Apps and Services 323.6K Reputation points MVP Volunteer Moderator
    2012-02-16T20:19:55+00:00

    What do you now have in the Private Sub UserForm_Initialize() event?

    You will need to replicate a good part of that to populate the second listbox from where ever the data for it resides.

    --
    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...

    You're going to hate me, but if it's any consolation, I love you so much for helping me several times on these forums lol

    Now the above has worked amazingly, but I'd now like a second list box to be used to reference another table with 2 columns and a continuously growing number of rows.  I would like the first row to appear in the list box to name each document (for which the text for that document is in column 2), but I'd like the second column not to show in the list box but when selected and used with a command button to appear in my template below the address.

     

    The source document will look something like this:

    Column1                                Column2

    DocName1                            "Content of letter1"

    DocName2                            "Content of letter2"

    DocName3                            "Content of letter3"

     

    So listbox2 will show:

    DocName1

    DocName2

    DocName3

    etc.

     

    When one of the above is selected, and used with a command button, I'd like the text contained in column 2 of the source document for the above to be placed at the docvariable field ( Docvariable Doctemplate ).

     

    I'm guessing you can use one command button to use both ListBox1 with the addresses and ListBox2 with the letter content at the same time, right?

     

    I tried duplicating the code in your last post for ListBox2 but nothing appears in ListBox2.  Should I just create another userform?  I mean, that's simple enough with what you've given to me already, it'd just be cleaner and more convenient to have this all on one form.

    ...

    Thanks again!! :-D

    http://answers.microsoft.com/message/24b2ed00-2972-4d72-a5c7-35af9063dc2e?threadId=9101a17a-9f74-4265-962e-fdd0eb6bfb56
    Meta tags: word; windows_7; office_2007

    Thu, 16 Feb 2012 16:59:37 +0000: CreateMessage LAssist2011
    Thu, 16 Feb 2012 17:14:24 +0000: Edit LAssist2011
    Thu, 16 Feb 2012 17:17:38 +0000: Edit LAssist2011

    Was this answer helpful?

    0 comments