Sneed
07-23-2013, 08:46 AM
Hello VBAX Forums,
I am having trouble getting a single listbox in a userform to populate more than one bookmark with conditional data in a Word Template. Below should explain a little better what I am trying to do.
Dear Client(Bookmark #1),
Sincerely,
Homer J. Simpson (bookmark #2)
Nuclear Safety Inspector (bookmark #3)
On the userform, I have a textbox for the client name and a listbox for the staff member name. Once the listbox staff member name is selected, the staff member name should populate bookmark #2 and the job title should automatically populate bookmark #3. The staff member and job title data are coming from a simple word table (with headers) that will be constantly updated.
The one thing that I am unable to work out is the code that calculates and displays the job title information (+1 column) based on the selected name from the userform. Any suggestions to clean up the code are also welcome. Thank you in advance for your advice. Option Explicit
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Initialize()
'Staff Name
Dim arrData() As String
Dim arrTitle() As String
Dim sourcedoc As Document
'Dim Title As String
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 your list member file and open the document
Set sourcedoc = Documents.Open(FileName:="C:\MSOffice\Data\AllNames.doc", Visible:=False)
'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 - 1
'Set the number of columns in the Listbox
ListBox1.ColumnCount = j
'Load list members into an array
ReDim arrData(i - 1, j - 1)
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
arrData(m, n) = myitem.Text
Next m
Next n
'Use the .List property to populate the listbox with the array data
ListBox1.List = arrData
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub cmdOK_Click()
Dim ClientName As Range
Set ClientName = ActiveDocument.Bookmarks("bClient").Range
ClientName.Text = Me.ListBox1.Value
Dim StaffName As Range
Set StaffName = ActiveDocument.Bookmarks("bStaff").Range
StaffName.Text = Me.TextBox1.Value
Me.Repaint
Letter.Hide
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
I am having trouble getting a single listbox in a userform to populate more than one bookmark with conditional data in a Word Template. Below should explain a little better what I am trying to do.
Dear Client(Bookmark #1),
Sincerely,
Homer J. Simpson (bookmark #2)
Nuclear Safety Inspector (bookmark #3)
On the userform, I have a textbox for the client name and a listbox for the staff member name. Once the listbox staff member name is selected, the staff member name should populate bookmark #2 and the job title should automatically populate bookmark #3. The staff member and job title data are coming from a simple word table (with headers) that will be constantly updated.
The one thing that I am unable to work out is the code that calculates and displays the job title information (+1 column) based on the selected name from the userform. Any suggestions to clean up the code are also welcome. Thank you in advance for your advice. Option Explicit
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Initialize()
'Staff Name
Dim arrData() As String
Dim arrTitle() As String
Dim sourcedoc As Document
'Dim Title As String
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 your list member file and open the document
Set sourcedoc = Documents.Open(FileName:="C:\MSOffice\Data\AllNames.doc", Visible:=False)
'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 - 1
'Set the number of columns in the Listbox
ListBox1.ColumnCount = j
'Load list members into an array
ReDim arrData(i - 1, j - 1)
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
arrData(m, n) = myitem.Text
Next m
Next n
'Use the .List property to populate the listbox with the array data
ListBox1.List = arrData
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub cmdOK_Click()
Dim ClientName As Range
Set ClientName = ActiveDocument.Bookmarks("bClient").Range
ClientName.Text = Me.ListBox1.Value
Dim StaffName As Range
Set StaffName = ActiveDocument.Bookmarks("bStaff").Range
StaffName.Text = Me.TextBox1.Value
Me.Repaint
Letter.Hide
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub