?
Solved

Create Table in Word using Listbox Items (VBA)

Posted on 2005-03-28
21
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month12 days, 1 hour left to enroll

752 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