Automating Templates - Continued

J

JeanneJo

Word 2007: have been working to build user form which is used to insert
information into company letterhead: specifically name, email address and,
when requested, the user's direct telephone number. I created a table in
Word containing employee complete names, their email addresses and the direct
phone numbers. The phone numbers aren't always included in the letter, but
the users want an option to select it depending on who they are writing to.

I have created the userform, have inserted code from the "Populate UserForm
Listbox" instructions, which has gotten me to the part where the information
contained in the Word table now populates the ListBox in the Userform. From
there I'm stuck: the complete names fill each of the designated bookmarks,
specifically named "Name," "Email" and "Directline" -

From what I have read, the procedures to populate the bookmarks is run by
the cmdClick: Following is the code I'm working with, which only works so
far. I also want to "hide" the Email and DirectLine columns of the Word
table and only have the names display in the ListBox.

If there is a better way of doing this, I'm certainly open to suggestions.
And thanks very much, in advance.

Jeanne.
 
G

Gordon Bentley-Mix

Jeanne,

Post your code and tell us how far is "so far".

As for hiding columns in the ListBox, set the column widths for the hidden
columns to 0 (zero).
 
J

JeanneJo

Oops - my code disappeared. Let me try that one again:

Private Sub cmdOK_Click()
Dim i As Integer
Dim Name As String
Dim oRng As Word.Range
Name = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Name = Name & ListBox1.Value
Else
Name = Name & ListBox1.Value
End If
Next i
'Set oRng = ActiveDocument.Bookmarks("Name").Range
'oRng.Text = Name
'ActiveDocument.Bookmarks.Add "Name", oRng
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng = oBM("Name").Range
oRng.Text = ListBox1.Text
Set oRng = oBM("Email").Range
oRng.Text = ListBox1.Text
Set oRng = oBM("DirectLine").Range
oRng.Text = ListBox1.Text
Me.Hide
End Sub

Thanks again.

Jeanne.
 
J

JeanneJo

And ... when I press F5 to "run" the code, I get my userform, populated with
the three fields of information contained in the Word table. That's a major
victory - took me days to get that far. When I select a name from that
listbox, and click on OK, the contents of the first column of the table, the
individual's "complete" name, i.e. John H. Doe, populates all three of the
bookmarks, including the Email bookmark and the DirectLine bookmark.

That's where I'm stuck - for now.
 
G

Greg Maxey

What code?

You hide columns by setting the columnwidth property to "0"

Private Sub UserForm_Initialize()
Dim sourcedoc As Document
Dim i As Integer, j As Integer
Dim myitem As Range
Dim m As Long, n As Long
Application.ScreenUpdating = False
'Open the file containing the table with items to load
Set sourcedoc = Documents.Open(FileName:="F:\Data
Stores\PopulateMultiColumnListBoxDataSource.docx")
'Get the number members = number of rows in the table of details less one
for the header row
i = sourcedoc.Tables(1).Rows.Count - 1
'Get the number of columns in the table of details
j = sourcedoc.Tables(1).Columns.Count
'Set the number of columns in the Listbox to match the number of columns in
the table of details
ListBox1.ColumnCount = j
'Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load table data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
'Load data into ListBox1
ListBox1.List() = MyArray
ListBox1.ColumnWidths = "50;0;0"
'Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
With ActiveDocument
FillBMs .Bookmarks("Name"), Me.ListBox1.Column(0)
FillBMs .Bookmarks("Address"), Me.ListBox1.Column(1)
FillBMs .Bookmarks("PhoneNo"), Me.ListBox1.Column(2)
End With
Me.Hide
End Sub

Sub FillBMs(ByRef Bm As Bookmark, pStr As String)
Dim oRng As Word.Range
Dim pName As String
Set oRng = Bm.Range
pName = Bm.Name
oRng.Text = pStr
ActiveDocument.Bookmarks.Add pName, oRng
End Sub
 
J

JeanneJo

Decided I ought to supply all of the code, instead of pieces. Might help
unraveling what's going on.

Private Sub cmdOK_Click()
Dim i As Integer
Dim Name As String
Dim oRng As Word.Range
Name = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Name = Name & ListBox1.Value
Else
Name = Name & ListBox1.Value
End If
Next i
'Set oRng = ActiveDocument.Bookmarks("Name").Range
'oRng.Text = Name
'ActiveDocument.Bookmarks.Add "Name", oRng
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng = oBM("Name").Range
oRng.Text = ListBox1.Text
Set oRng = oBM("Email").Range
oRng.Text = ListBox1.Text
Set oRng = oBM("DirectLine").Range
oRng.Text = ListBox1.Text
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myItem As Range
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
'Modify the following line to point to file and open document
Set sourcedoc = Documents.Open(FileName:="F:\Apps\Word Templates\Moulton
Templates\Names.docx")
'Get the number of list members (i.e. table rows - 1 if header row is used)
i = sourcedoc.Tables(1).Rows.Count - 1
'Get the number of list member attributes (i.e. table columns)
j = sourcedoc.Tables(1).Columns.Count
'Set the number of columns in the Listbox
ListBox1.ColumnCount = j
'Load list members into an array
ReDim myArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myItem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myItem.End = myItem.End - 1
myArray(m, n) = myItem.Text
Next m
Next n
'Populate the ListBox using the array
ListBox1.List() = myArray
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
 
D

Doug Robbins - Word MVP

You need to set the bound column attribute for each column in turn to access
the data in it.

Private Sub cmdOK_Click()

With ActiveDocument
ListBox1.BoundColumn = 1
.Bookmarks("Name").Range.InsertBefore Listbox1.Value
ListBox1.BoundColumn = 2
.Bookmarks("Email").Range.InsertBefore Listbox1.Value
ListBox1.BoundColumn = 3
.Bookmarks("DirectLine").Range.InsertBefore Listbox1.Value
End With
Me.Hide

End Sub

Or you can use the .List(row#, column#) property of the listbox
Private Sub cmdOK_Click()

With ActiveDocument
.Bookmarks("Name").Range.InsertBefore Listbox1.List(.ListIndex, 0)
.Bookmarks("Email").Range.InsertBefore Listbox1.List(.ListIndex, 1)
.Bookmarks("DirectLine").Range.InsertBefore Listbox1.List(.ListIndex, 2)
End With
Me.Hide

End Sub
..List(.ListIndex, 0)


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.
 
J

JeanneJo

Hi Doug, and thank you very much. It's amazing how well things work when you
have the right code! Would you have a quick recommendation on how to address
the direct line question? I want the default to be "no," don't insert it;
and have an option button or a checkmark which, when tagged, will insert the
number.

And thanks again, Doug, for helping me out. I don't have a book; I'm just
reading, reading, reading ... this website as well as publications by Greg
Maxey and anyone else who has information available on the web. It's slow
going, but it is coming together, thanks to you and other Microsoft gurus.
Thank goodness you're out there.

JeanneJo
 
J

JeanneJo

Never mind - I got the checkmark option to work! Whew. I'm sure I'm going
to have a ton of other questions as I proceed, but for now, I have an
automated letterhead that automatically inserts information about the author
- couldn't be happier.

Thanks again to Doug and Gordon; and Gordon, thanks to your "hide"
suggestion, I figured out how to hide the two columns of information I didn't
want to display in the listbox. Another triumph, thanks to "try this"
information from this site.

JeanneJo
 
J

JeanneJo

Thank you Greg - I have updated my copy of your Populate UserForm Listbox
publication. Appreciate your help very much.

JeanneJo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Optionbutton Not working 3
Case Statement - Error handling 1
Auto populate value from one task to another in MS PROJECT 0
Multiple Email Accounts and Calendars 0
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
How do I set focus (and select text) on a text box on a modeless userform? 0
Crating User Form to Add Names 1
Weekly Report 2

Top