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

Anonymous

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!

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

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.

0 comments

Answer accepted by question author

Anonymous

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

0 comments

42 additional answers

Anonymous

2012-03-09T18:40:17+00:00

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
Was this answer helpful?

0 comments No comments
Anonymous

2012-03-09T07:35:36+00:00

For just one text box using the whole of the sheet from A1 then the following should fit.

Also unload your form rather than hide it when you click Cancel

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("D:\My Documents\Test\Datasheet.xlsx")

Set xlWS = xlWB.Worksheets(1)

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

cCols = xlWS.Cells(1, xlWS.Columns.Count).End(xlToLeft).Column

ListBox1.ColumnCount = cCols

With Me.ListBox1

    .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

End Sub
Was this answer helpful?

0 comments No comments
Anonymous

2012-03-08T14:55:53+00:00

I'm sorry, I can't seem to incorporate what's on your page with my current code. I'm trying to use this one:

Private Sub Userform_Initialize()

'Late binding. No reference to Excel Object required.

Dim xlApp As Object

Dim xlWB As Object

Dim xlWS As Object

Dim cRows As Long

Dim i As Long

Set xlApp = CreateObject("Excel.Application")

'Open the spreadsheet to get data

Set xlWB = xlApp.Workbooks.Open("D:\Data Stores\sourceSpreadsheet.xls")

Set xlWS = xlWB.Worksheets(1)

cRows = xlWS.Range("mySSRange").Rows.Count - xlWS.Range("mySSRange").Row + 1

ListBox1.ColumnCount = 3

'Populate the listbox.

With Me.ListBox1

    For i = 2 To cRows

       'Use .AddItem property to add a new row for each record and populate column 0

      .AddItem xlWS.Range("mySSRange").Cells(i, 1)

      'Use .List method to populate the remaining columns

      .List(.ListCount - 1, 1) = xlWS.Range("mySSRange").Cells(i, 2)

      .List(.ListCount - 1, 2) = xlWS.Range("mySSRange").Cells(i, 3)

    Next i

End With

'Clean up

Set xlWS = Nothing

Set xlWB = Nothing

xlApp.Quit

Set xlApp = Nothing

lbl_Exit:

Exit Sub

End Sub

Below is my current code (Not sure if you would need to see the entire thing or just the initialize event so I copied everything below). I would like to change oSource2 to use your example above.

Option Explicit

Private oVars As Variables

Private oSource1 As Document, oSource2 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()

    Me.hide

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("CFName").Value = Me.ListBox2.Column(0)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

oVars("18").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()**Set oSource1 = Documents.Open(FileName:="C:\Templates\TemplateDocs\Addresses.doc", _

                              AddToRecentFiles:=False, _

                              Visible:=False)

Set oSource2 = Documents.Open(FileName:="C:\Templates\TemplateDocs\ClientInfo.doc", _

                              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

Set oTable = oSource2.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.ListBox2

.ColumnCount = j 'The number of columns in the table

    strWidth = (.Width / 2) - 2 & ";" & (.Width / 2) - 2 'Set the first two column widths to suit the available space - you can use actual measurements if you prefer then to calculated measurements

    For k = 3 To j 'set columns 3 to the total to 0 width

        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

'Close the source file

oSource1.Close SaveChanges:=wdDoNotSaveChanges

oSource2.Close SaveChanges:=wdDoNotSaveChanges

End With

Thank you!
Was this answer helpful?

0 comments No comments