Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create Table in Word using Listbox Items (VBA)

Posted on 2005-03-28
21
Medium Priority
?
317 Views
Last Modified: 2010-05-18
Hey Experts,

I need help creating a table in word using items from a multicolumn listbox.

My listbox has three columns the first is item second is description and third is quantity.  If i have 10 items in my listbox I want to be able to create a table with 10 rows with 3 columns each. if the number of items in my listbox would change to 20 items then i'll create a table with 20 rows with 3 columns each.  Basicly I want the table to be a copy of the listbox.

sorry if its confusing
0
Comment
Question by:Jason_Kha
  • 11
  • 10
21 Comments
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13645767
Hi Jason_Kha,

The sub below will take a multicolumn listbox from a userform, make  a table at the current cursor location and fill it with the data from the listbox. This is for Word VBA.

Public Sub MakeTable()
    Dim MyArray As Variant
    Dim AddFor0Base As Integer
    MyArray = UserForm1.ListBox1.List
    AddFor0Base = 0
    If LBound(MyArray, 2) = 0 Then
        AddFor0Base = 1
    End If
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=UBound(MyArray, 1) + AddFor0Base, NumColumns:= _
        UBound(MyArray, 2) + AddFor0Base, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    DisOne = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(DisOne)
        For i = LBound(MyArray, 1) To UBound(MyArray, 1)
            For j = LBound(MyArray, 2) To UBound(MyArray, 2)
                .Cell(i + AddFor0Base, j + AddFor0Base).Range.Text = MyArray(i, j)
            Next
        Next
    End With
End Sub

HTH
dragontooth

0
 

Author Comment

by:Jason_Kha
ID: 13646828
I get an error that DisOne 'variable not defined'
0
 

Author Comment

by:Jason_Kha
ID: 13646845
I and J also not defined what should i define them as
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13646858
Public Sub MakeTable()
    Dim MyArray As Variant
    Dim AddFor0Base As Integer
    Dim DisOne As Integer     '< added this line
    MyArray = UserForm1.ListBox1.List
    AddFor0Base = 0
    If LBound(MyArray, 2) = 0 Then
        AddFor0Base = 1
    End If
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=UBound(MyArray, 1) + AddFor0Base, NumColumns:= _
        UBound(MyArray, 2) + AddFor0Base, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    DisOne = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(DisOne)
        For i = LBound(MyArray, 1) To UBound(MyArray, 1)
            For j = LBound(MyArray, 2) To UBound(MyArray, 2)
                .Cell(i + AddFor0Base, j + AddFor0Base).Range.Text = MyArray(i, j)
            Next
        Next
    End With
End Sub


HTH
dragontooth

0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13646872
Public Sub MakeTable()
    Dim MyArray As Variant
    Dim AddFor0Base As Integer
    Dim DisOne As Integer     '< added this line
    Dim i As Integer     '< added this line
    Dim j As Integer     '< added this line
    MyArray = UserForm1.ListBox1.List
    AddFor0Base = 0
    If LBound(MyArray, 2) = 0 Then
        AddFor0Base = 1
    End If
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=UBound(MyArray, 1) + AddFor0Base, NumColumns:= _
        UBound(MyArray, 2) + AddFor0Base, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    DisOne = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(DisOne)
        For i = LBound(MyArray, 1) To UBound(MyArray, 1)
            For j = LBound(MyArray, 2) To UBound(MyArray, 2)
                .Cell(i + AddFor0Base, j + AddFor0Base).Range.Text = MyArray(i, j)
            Next
        Next
    End With
End Sub


HTH
dragontooth
0
 

Author Comment

by:Jason_Kha
ID: 13646893
.Cell(i + AddFor0Base, j + AddFor0Base).Range.Text = MyArray(i, j)

this line give me the error invalid use of null
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13647023
MyArray has the exact same information in it as the listbox does. So that means the listbox has null information. So here is the check for empty and null.

Public Sub MakeTable()
    Dim MyArray As Variant
    Dim AddFor0Base As Integer
    Dim DisOne As Integer
    Dim I As Integer
    Dim J As Integer
    MyArray = UserForm1.ListBox1.List
    AddFor0Base = 0
    If LBound(MyArray, 2) = 0 Then
        AddFor0Base = 1
    End If
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=UBound(MyArray, 1) + AddFor0Base, NumColumns:= _
        UBound(MyArray, 2) + AddFor0Base, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    DisOne = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(DisOne)
        For I = LBound(MyArray, 1) To UBound(MyArray, 1)
            For J = LBound(MyArray, 2) To UBound(MyArray, 2)
                If Not IsEmpty(MyArray(I, J)) And Not IsNull(MyArray(I, J)) Then
                    .Cell(I + AddFor0Base, J + AddFor0Base).Range.Text = MyArray(I, J)
                End If
            Next
        Next
    End With
End Sub

hth
dragontooth

0
 

Author Comment

by:Jason_Kha
ID: 13647085
still one more thing

i have three columns in my listbox but when it creates the table the table has 10 columns
0
 

Author Comment

by:Jason_Kha
ID: 13647127
what do i need to add to change the formatting of the table.  for instance i want the table to be times new roman, 10pt and how do i make it so that the the rows are a certain width.
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13647149
I will need to see the document now. see profile for sending location. I am thinking the listbox has 10 columns but only 3 columns are filled, but that is just a guess.

dragontooth

0
 

Author Comment

by:Jason_Kha
ID: 13647155
yep thats right
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13647192
for the formating

With ActiveDocument.Tables(DisOne)
        .Range.Font.Name = "Times New Roman"   '< here is the font
        .Range.Font.Size = 10                               '< here is the font size
        .Rows.Height = 15                                     '< this is in points for the row height
        For I = LBound(MyArray, 1) To UBound(MyArray, 1)
            For J = LBound(MyArray, 2) To UBound(MyArray, 2)
                If Not IsEmpty(MyArray(I, J)) And Not IsNull(MyArray(I, J)) Then
                    .Cell(I + AddFor0Base, J + AddFor0Base).Range.Text = MyArray(I, J)
                End If
            Next
        Next
    End With
0
 

Author Comment

by:Jason_Kha
ID: 13647199
do you still want to see the doc.
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 2000 total points
ID: 13647302
Depends on how you would like to handle the 10 columns verses the 3 columns.

if you just want 3 columns even if the listbox has 10 then this will fix that.

Public Sub MakeTable()
    Dim MyArray As Variant
    Dim AddFor0Base As Integer
    Dim DisOne As Integer
    Dim I As Integer
    Dim J As Integer
    MyArray = UserForm1.ListBox1.List
    AddFor0Base = 0
    If LBound(MyArray, 2) = 0 Then
        AddFor0Base = 1
    End If
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=UBound(MyArray, 1) + AddFor0Base, NumColumns:= _
        2 + AddFor0Base, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    DisOne = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(DisOne)
        .Range.Font.Name = "Times New Roman"
        .Range.Font.Size = 10
        .Rows.Height = 15 'this is in points
        For I = LBound(MyArray, 1) To UBound(MyArray, 1)
            For J = LBound(MyArray, 2) To 2    '< this will need to be 3 if option base 1 is used
                If Not IsEmpty(MyArray(I, J)) And Not IsNull(MyArray(I, J)) Then
                    .Cell(I + AddFor0Base, J + AddFor0Base).Range.Text = MyArray(I, J)
                End If
            Next
        Next
    End With
End Sub

dragontooth

0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13655921
How is it going?

dragontooth

0
 

Author Comment

by:Jason_Kha
ID: 13663730
it works greats Thanks Alot!
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13663784
So the 3 column vs 10 column problem doesn't apply anymore?
0
 

Author Comment

by:Jason_Kha
ID: 13663845
no
0
 

Author Comment

by:Jason_Kha
ID: 13663860
how would you set the table position so that the left side of the table is say for instance .25 inch from the left margin
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 13664596
Th only way I know is setting the actual margin at 0.25 which would effect the whole doc.
0
 

Author Comment

by:Jason_Kha
ID: 13664718
yeah i figured that just wondering if you knew
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question