flohrstw
08-13-2012, 12:07 PM
Finally getting closer to fixing my overall problem. I have a ListBox in a UserForm that is populated from a Source Document. Upon running the template, the ListBox is now being populated as it should. Now I need to limit the data shown in the ListBox to the first column which is the name of the individual. All of the other corresponding data is for addresses, phone numbers, etc... My goal is to allow the user to select a name from the ListBox in the UserForm and have to populate the Word doc with the corresponding information on CmdSubmit. Here is what I have so far. Any help would be greatly appreciated!
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub
Private Sub CmdSubmit_Click()
With ActiveDocument
.FormFields("Date").Result = txtDate.Value
.FormFields("To").Result = txtTo.Value
.FormFields("Facility").Result = txtFacility.Value
.FormFields("Treatment_Date").Result = txtTreatment_Date.Value
.FormFields("Name").Result = txtName.Value
.FormFields("DOB").Result = txtDOB.Value
.FormFields("Address").Result = txtAddress.Value
.FormFields("Treatment").Result = txtTreatment.Value
End With
Application.ScreenUpdating = True
Unload Me
Exit Sub
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To ListBoxOfficer.ColumnCount
ListBoxOfficer.BoundColumn = i
Select Case True
Case i = ListBoxOfficer.ColumnCount - 1
Client = Client & ListBoxOfficer.Value & " "
Case i = ListBoxOfficer.ColumnCount
Client = Client & ListBoxOfficer.Value & vbCr
Case Else
Client = Client & ListBoxOfficer.Value & vbCr & vbTab
End Select
Next i
Set oRng = ActiveDocument.Bookmarks("Client").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Client", oRng
Me.Hide
lbl_Exit:
Exit Sub
End Sub
Private Sub UserForm_Initialize()
Dim arrData() As String
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
Set sourcedoc = Documents.Open(FileName:="F:\All\Templates\sourcedoc.docx", Visible:=False)
i = sourcedoc.Tables(1).Rows.Count - 1
j = sourcedoc.Tables(1).Columns.Count
ListBoxOfficer.ColumnCount = j
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
ListBoxOfficer.List = arrData
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
lbl_Exit:
Exit Sub
End Sub
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub
Private Sub CmdSubmit_Click()
With ActiveDocument
.FormFields("Date").Result = txtDate.Value
.FormFields("To").Result = txtTo.Value
.FormFields("Facility").Result = txtFacility.Value
.FormFields("Treatment_Date").Result = txtTreatment_Date.Value
.FormFields("Name").Result = txtName.Value
.FormFields("DOB").Result = txtDOB.Value
.FormFields("Address").Result = txtAddress.Value
.FormFields("Treatment").Result = txtTreatment.Value
End With
Application.ScreenUpdating = True
Unload Me
Exit Sub
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To ListBoxOfficer.ColumnCount
ListBoxOfficer.BoundColumn = i
Select Case True
Case i = ListBoxOfficer.ColumnCount - 1
Client = Client & ListBoxOfficer.Value & " "
Case i = ListBoxOfficer.ColumnCount
Client = Client & ListBoxOfficer.Value & vbCr
Case Else
Client = Client & ListBoxOfficer.Value & vbCr & vbTab
End Select
Next i
Set oRng = ActiveDocument.Bookmarks("Client").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Client", oRng
Me.Hide
lbl_Exit:
Exit Sub
End Sub
Private Sub UserForm_Initialize()
Dim arrData() As String
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
Set sourcedoc = Documents.Open(FileName:="F:\All\Templates\sourcedoc.docx", Visible:=False)
i = sourcedoc.Tables(1).Rows.Count - 1
j = sourcedoc.Tables(1).Columns.Count
ListBoxOfficer.ColumnCount = j
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
ListBoxOfficer.List = arrData
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
lbl_Exit:
Exit Sub
End Sub