Thank you. Apologies for leaving out important info in the first post. I
certainly understand where you're coming from and will include all relevant
info with future posts - thanks for giving me that nudge
Set out below is what I'd like to be able to achieve, what I have done so
far and what I need some further help with.
What I'd like to be able to achieve (using Word 97) is:
1. Have a userform with:
a. Combobox1 with options 1, 2, 3.
b. Comboxbox2 that is linked to whatever is selected in combobox1 with a
further 3 options (A, B, C) for each option 1, 2, 3 (in other words: 1A, 1B,
1C, 2A, 2B, 2C, 3A, 3B, 3C).
c. Listbox1 that is populated depending on which combobox2 option is
selected. For example, if 1A is selected, listbox1 is populated with rows of
data (names and addresses) from table 1A in the source document. If 1B is
selected, listbox1 is populated from table 1B in the source document. Etc.
d. A user can select one or more rows from listbox1.
e. Commandbutton1 is clicked, which inserts the selected rows into a table
in the active Word document.
2. If at all possible, have tables 1A, 1B, 1C, 2A, 2B, 2C, 3A, 3B, 3C in
the one source document (for ease of maintenance - rather than 9 separate
documents).
Where I am up to so far (and what I need some further help with):
As an absolute beginner to VisualBasic, but with the generous assistance
over the last week of yourself, Doug Robbins and others (and with the benefit
of reading the links that have been provided), I have gotten as far as the
following:
a. Combobox1 with options 1, 2, 3.
DONE (I think):
Private Sub UserForm_Initialize()
With ComboBox1
..AddItem "1"
..AddItem "2"
..AddItem "3"
End With
End Sub
b. Comboxbox2 that is linked to whatever is selected in combobox1 with a
further 3 options (A, B, C) for each option 1, 2, 3 (in other words: 1A, 1B,
1C, 2A, 2B, 2C, 3A, 3B, 3C).
ONLY PARTLY DONE.
Private Sub UserForm_Initialize()
With ComboBox2
..AddItem "A"
..AddItem "B"
..AddItem "C"
End With
End Sub
I still haven't worked out how to do the code for the change event(?) for
combobox2 that links it to combobox1. Any assistance with that would be
greatly appreciated.
c. Listbox1 that is populated depending on which combobox2 option is
selected. For example, if 1A is selected, listbox1 is populated with rows of
data (names and addresses) from table 1A in the source document. If 1B is
selected, listbox1 is populated from table 1B in the source document. Etc.
ONLY PARTLY DONE. This was my original code. But that brings up source
document as a whole:
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 your list member file and open the
document
Set sourcedoc = Documents.Open(FileName:="C:\sourceWord.doc")
'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 attritbutes (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
What I haven't yet achieved for listbox1 is:
i. Making what appears in the listbox contingent on what option is selected
in combobox2; and
ii. Working out whether the code can populate from only one table in the one
source document, depending on which combobox2 option is selected.
I thought maybe the bookmark idea? Any assistance on what code I need to do
this would be greatly appreciated
d. A user can select one or more rows from listbox1.
DONE. I made the exit event of the listbox a multi-select (as suggested by
Doug Robbins).
e. Commandbutton1 is clicked, which inserts the selected rows into the Word
document.
DONE. I came up with the following code which generates a table in the
active Word document and inserts the selected listbox1 rows into that table:
Private Sub CommandButton1_Click()
Dim x As Long, y As Long, tbl As Table, n As Integer
x = ListBox1.ColumnCount + 1
' Count the number of selected records for creation of the table
n = 1
For y = 2 To ListBox1.ListCount + 1
If ListBox1.Selected(y - 2) = True Then
n = n + 1
End If
Next y
Selection.TypeParagraph
Set tbl = ActiveDocument.Tables.Add(Selection.Range, n, x)
tbl.Range.Borders.Enable = False
Dim tblcell As Cell
For Each tblcell In tbl.Range.Cells
tblcell.Borders.Enable = False
Next tblcell
tbl.Columns(1).Width = CentimetersToPoints(2.7)
tbl.Columns(2).Width = CentimetersToPoints(4.75)
tbl.Columns(3).Width = CentimetersToPoints(3.25)
tbl.Columns(4).Width = CentimetersToPoints(3.25)
tbl.Columns(5).Width = CentimetersToPoints(3.25)
Dim rg As Range
tbl.Columns(4).Select
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
tbl.Columns(5).Select
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Dim rgStart As Range, rgEnd As Range
tbl.Range.Font.Size = 11
tbl.Range.Font.Name = "Times New Roman"
tbl.Range.ParagraphFormat.SpaceBefore = 2
tbl.Range.ParagraphFormat.SpaceAfter = 2
tbl.Range.ParagraphFormat.LineSpacing = 12
' Insert selected listbox1 rows
For y = 2 To 5 'ListBox1.ColumnCount
n = 1
For x = 2 To ListBox1.ListCount + 1
If ListBox1.Selected(x - 2) = True Then
n = n + 1
tbl.Cell(n, y).Range.Text = ListBox1.List(x - 2, y - 2)
End If
Next x
Next y
Unload Me
End Sub
Sorry about the long post. I hope I've expressed specifically enough what
I'd like to achieve and what I'm struggling with. I would be extremely
grateful for any pointers about what I need to do to achieve the outcome (if
its possible).
My sincere thanks
