Populating a Table

L

LEU

I am having problems populating a table from the form I have created. Doug
Robbins posted that I look at Greg Maxey’s website. I did this but I am still
having trouble. With the following macros I can create my table and open the
form with the info to populate the table. The list boxes in the form are
loaded from a table in a Word document on my C drive. What I want to do is
pick one or more of the choices in the first list box and it would then load
the choice(s) in the first list box into the first column of the table and
the info from the associated second list box into the second column of the
table. If I picked more than one item in the list box then it would load my
second choice in the second row column one and column two and so on.

Here are my macros:

Sub WarningBox()
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
1, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:=wdAutoFitFixed
Selection.Tables(1).Rows.SetLeftIndent LeftIndent:=8, RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(1).SetWidth ColumnWidth:=432,
RulerStyle:=wdAdjustNone
Selection.Tables(1).Borders.OutsideLineWidth = wdLineWidth150pt
Selection.ParagraphFormat.Style = "Normal"
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Font.Size = 18
Selection.Font.Color = wdColorRed
Selection.Font.Bold = wdToggle
Selection.Font.Underline = wdUnderlineSingle
Selection.TypeText Text:="Warning"
Selection.Font.Underline = wdUnderlineNone
Selection.Font.Bold = wdToggle
Selection.TypeParagraph
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeBackspace
With Selection.ParagraphFormat
.LeftIndent = InchesToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 6
.SpaceAfterAuto = False
End With
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.Cells.Split NumRows:=1, NumColumns:=2, MergeBeforeSplit:=False
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.MoveRight Unit:=wdCell
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="POTENTIAL HAZARDS"
Selection.MoveRight Unit:=wdCell
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="CONTROLS"
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.Cells.Split NumRows:=1, NumColumns:=2, MergeBeforeSplit:=False
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveDown Unit:=wdLine, Count:=1
With ActiveDocument.Bookmarks
.Add Range:=Selection.Range, Name:="Hazard1"
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.MoveRight Unit:=wdCell
With ActiveDocument.Bookmarks
.Add Range:=Selection.Range, Name:="Controls1"
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
UserForm1.Show
End Sub


Here are my forms macros:

Option Explicit

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
Set sourcedoc = Documents.Open(FileName:="C:\Hazard.doc", Visible:=False)
i = sourcedoc.Tables(1).Rows.Count - 1
j = sourcedoc.Tables(1).Columns.Count
ListBox1.ColumnCount = j
'Hide column 2
ListBox1.ColumnWidths = "75;0"
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
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub


Private Sub CommandButton1_Click()
Dim i As Integer, Hazards As String, Controls As String
Hazards = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If ListBox1.Value <> "" Then
Hazards = Hazards & ListBox1.Value '& vbCr
FillBookmark "Hazard1", Hazards
End If
Next i
FillBookmark "Hazard1", Hazards
Controls = ""
For i = 1 To ListBox2.ColumnCount
ListBox2.BoundColumn = i
If ListBox2.Value <> "" Then
Controls = Controls & ListBox2.Value '& vbCr
FillBookmark "Controls1", Controls
End If
Next i
FillBookmark "Controls1", Controls
Me.Hide
End Sub


Private Sub ListBox1_Change()
Dim myArray As Variant
myArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13))
ListBox2.List = myArray
End Sub


Private Sub ListBox2_Change()
Dim i As Long
Dim lngCount As Long
lngCount = 0
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) Then
lngCount = lngCount + 1
End If
Next i
End Sub


Private Sub FillBookmark(bkName As String, bkVal As String)
Dim oRg As Range
With ActiveDocument.Bookmarks
If .Exists(bkName) Then
Set oRg = .Item(bkName).Range
oRg.Text = bkVal
..Add Name:=bkName, Range:=oRg
End If
End With
End Sub

Here is an example of my table info from the document on my C drive that I
am loading into the form:

Row One
Column One = Hazard
Column Two = Controls

Row Two
Column One = Chemical Exposure Hazard
Column Two = Avoid contact with eyes.
Avoid breathing vapor or mist.
Wash hands thoroughly after use.
Review MSDS for each chemical
Locate pressurized portable eye wash station near work area.
Use nitrile gloves when applying chemicals

Row Three
Column One = Electrical Hazards
Column Two = Inspect tools/equipment for damage prior to use. Do not use
damaged tools or equipment
Use ground fault circuit interrupters (GFCI).


LEU
 
A

alborg

Hi Leu:

I made it work! You might need to change it according to your needs, but I
was able to pull the data from C:\Hazard.doc, populate a UserForm listbox,
then use that data to populate a table on your template!

1) First, the working code-

Option Explicit
Dim rownum As Long, i As Long, ii As Long
Private Sub CommandButton1_Click()
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:=1,
DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
Selection.Tables(1).Rows.SetLeftIndent LeftIndent:=8, RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(1).SetWidth ColumnWidth:=432,
RulerStyle:=wdAdjustNone
Selection.Tables(1).Borders.OutsideLineWidth = wdLineWidth150pt
Selection.ParagraphFormat.Style = "Normal"
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Font.Size = 18
Selection.Font.Color = wdColorRed
Selection.Font.Bold = wdToggle
Selection.Font.Underline = wdUnderlineSingle
Selection.TypeText Text:="Warning"
Selection.Font.Underline = wdUnderlineNone
Selection.Font.Bold = wdToggle
Selection.TypeParagraph
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeBackspace
With Selection.ParagraphFormat
.LeftIndent = InchesToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 6
.SpaceAfterAuto = False
End With
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.Cells.Split NumRows:=1, NumColumns:=2, MergeBeforeSplit:=False
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.MoveRight Unit:=wdCell
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="POTENTIAL HAZARDS"
Selection.MoveRight Unit:=wdCell
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="CONTROLS"
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.Cells.Split NumRows:=1, NumColumns:=2, MergeBeforeSplit:=False
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveDown Unit:=wdLine, Count:=1
rownum = ActiveDocument.Tables(1).Rows.Count + 3
i = 0
ii = 3
Do Until ii > rownum
ActiveDocument.Tables(1).Rows.Add
ActiveDocument.Tables(1).Cell(ii, 0).Range = UserForm1.ListBox1.List(i)
ii = ii + 1
i = i + 1
Loop
Exit Sub
'UserForm1.Show.... this gave an error
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
Set sourcedoc = Documents.Open(FileName:="C:\Hazard.doc", Visible:=False)
i = sourcedoc.Tables(1).Rows.Count
j = sourcedoc.Tables(1).Columns.Count
ListBox1.ColumnCount = j
'Hide column 2
ListBox1.ColumnWidths = "75;0"
ReDim myArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 1, n + 1).Range 'note that
you had an error on this line
myitem.End = myitem.End - 1
myArray(m, n) = myitem.Text
Next m
Next n
'Load data into ListBox1
ListBox1.List() = myArray
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

2) If you wish to look at the template that I put together, using your
original code, together with the Hazards.doc document that supplied the
data, download the zipped file from here-

http://www.box.net/shared/static/azzz9kntki.zip

Cheers,
Al
 
L

LEU

Hi Al,

Thank you for your help.

I am at work today and can not get to your zipped file. I'll take a look at
it tonight when I get home.


Larry
 
L

LEU

Hi Al,

I was able to get your zipped file and I tried to run it. It gave me the
following error:

Could not get the List property. Invalid property array index.

at the following line:

ActiveDocument.Tables(1).Cell(ii, 0).Range = UserForm1.ListBox1.List(i)

Larry
 
A

alborg

Hi Leu:

My fault- the thing had 2 bugs-

1) I randomly put a clickbutton amongst the toolbars on my laptop when I
should have made a special group for that particular template.
2) I should have done a VBA UnLoad event to close the UserForm once the
table was populated on the template. You see, you started clicking on the
clickbutton several times thinking nothing got done. You should have clicked
on it once, then clicked on the UserForm's "X" to close it. The table
magically appears once the UserForm disappears. I fixed it- it is unloaded
programmatically.

I also placed the instructions on the template itself-
___________________________________
For all ye downloaders:

1) Make sure that the file “Hazard.doc†is in the C:\ directory.
2) I had a minor bug: I accidentally placed the put the
Project.Module1.BringUpUF macro toolbar button randomly onto one of my
laptop’s toolbar groups. Well, there is now a FillaTable toolbar group. If
you don’t see the FillaTable program group in the toolbar, click on View->
Toolbars-> FillaTable. When you click on the Project.Module1.BringUpUF
button, a popup will be shown.
3) The error that you got, LEU, is that you doubleclicked on the UserForm’s
clickbutton, placing a second table on the document, thus royally screwing
things up. You should have clicked on the button once, then click on the
UserForm’s right upper “X†to unload the UserForm. Anyhow, it was my error- I
now close UserForm1 through an UnLoad event.
___________________________________

URL for download of fixed template:
http://www.box.net/shared/static/azzz9kntki.zip

That's it!

Cheers,
Al
 
L

LEU

Al'

I downloaded your zipped file and tried it. It does not do what I'm tring to
do. Is there a way I could send you my files so you could see what I'm doing?

Larry
 
B

Biyi

Hello,
Thank you so much for posting this on the website, I've been following your instructions. However, I got the same problem that Larry had, which was:

Could not get the List property. Invalid property array index.

at the following line:

ActiveDocument.Tables(1).Cell(ii, 0).Range = UserForm1.ListBox1.List(i)

I know you had a private chat with him, but could you help me on this please?
Also, I didn't get my information from a source document, I actually used a "add" command button to add items into my listbox, and now I just wanted to put that listbox items (with bullets) into an already created table.

Thanks a lot for your help
 

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

Word macro that can call cells out of Excel for a find and replace 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Multiple Tables 2
Making a macro only work on certain files 2
Macro to Select text from a point given to a specific word 2
Remove Identical words 0
VBA Coding Help for Beginner 0
ListBox problem 2

Top