[RESOLVED] How to add text to table cells in Word 2003

[New2vba]

This may seem rather trivial, but I would appreciate any help.

I am currently building a Word 2003 template. When the template is opened (new), a userform is initialised. Choices made within the userform will build the completed document using bookmarks.

Within the document I have a 1 row, 2 column table. I want to use the table to generate 2 lists of items - one in the left column and one in the right column (it is in fact one list, but I am separating into 2 columns for spacing). Each item in the list needs to be in its own cell. Before the user has made any choices I do not know how long the list will be, although I know there will be at least one item.

In the first cell is a bookmark, from which my list begins. The first item is entered based on a user choice. The remaining items may or may not be entered so for each option that is selected I have created a string which is in the format:

strOption02 = vbTab & "Some text"

If the option is not selected then the string is nothing.

When the userform information is submitted, I then have:

.Bookmark("Table1Cell1").Range.Text = strFirstItem & strOption01 & strOption02.....& strOption50

Any strings that are null are ignored so the table does not expand beyond the size it needs to be and the length of the "2" lists would be the same or worst case, the left column would have one entry more (or at least that was the plan).

I was hoping that the vbTab would behave the same way as pressing the keyboard tab when in a table cell, i.e. tab to the next column or add a new row if already in the last column. As I'm sure many people know (except me of course), that doesn't work.

Assuming my explanation is clear, can anyone offer any advice or direction with what I am trying to achieve?

Thanks in advance.

PS. I have tried recording a Macro tabbing from cells and adding text etc but I don't understand the code enough to apply it properly (constant syntax errors).

[RobDog888]

Use the Tables collection to add text to a particular cell.

[New2vba]

Use the Tables collection to add text to a particular cell.

Thanks for the response RobDog, but I can't see how that will work because the options within my userform not only add the text to the cell, but also determine whether the cell is there in the first place.

Say for instance, I have 50 options, but only options 09, 23, and 32 are selected. Then, assuming what I am trying is feasible:

My 1 row, 2 column tabke will become a 2 row, 2 column table.

Cell(1,1) = strFirstItem (something is always inserted here)
Cell(1,2) = text for option 09
Cell(2,1) = text for option 23
Cell(2,2) = text for option 32

However, in another case the user may select options 03, 07, 15, 23, 27, 48 and 50, which would produce a 4 row, 2 column table.

My problem is ignoring an option if nothing is selected and putting them in order without leaving blank spaces if something is selected.

Hope this makes sense.

[RobDog888]

I dont quite understand but I think if you look at the table collection you will realise that there is a Row and Columns child collections. You can check what the dimensions of the table are dynamically. So it wont matter what options are choosen.

[New2vba]

Thanks again RobDog, but maybe this one is a little beyond me for now. I have looked at Tables in vba help and google and can not find a way of achieving my goal (I have to plead ignorance at this point ).

To Confirm:

My table will always contain 2 columns, but the number of rows will vary in-line with the number of options selected.

With each option selected, I want to add the option text either in column 2 of the last row or, if that cell already contains an option, then I want the option to add a row and then add text into the first column of the new row. After that the next selected option will move to the second column of the new row and add the option text. The cycle will then repeat for as many options as there are selected.

So each option will do one of two things, depending on where the last entry was made, i.e. column 1 or column 2.

Move from column 1 of the last row to column 2 of the last row, where it will add option text.

OR

Add a row and then add option text to the first column of that row.

Perhaps spending more time on the tables collection will reveal a solution?

Thanks again for your time.

[RobDog888]

Ok, I was just trying to get you to see the forest instead of the trees.

The Tables' Rows collection has a .Count property. This will tell you how many rows exist in your table. You can reference any row by changing the index property of the Rows collection.

MsgBox "Rows in the first table: " & Tables(1).Rows.Count
 
Tables(1).Rows(Tables(1).Rows.Count).Cells(2).Range.Text = "Column 2"

[New2vba]

Thanks.

I've looked at the tables collection and am starting to understand it a little better. Still not quite sure exactly how to achieve my goal, but at least I have some direction.

Not sure if it's possible or how to do it, but one route I was going to attempt was to somehow count the total number of selected options and then assign a resulting value to strings, which follow a numerical order. Based on the total number of options selected, I will generate a table with an equal or similar number of cells (I say similar because if the total number of options is odd, then the last cell, last column will be empty). I then need to assign the strings to cells in order.

For example:

If the user selects option04, option08, option12 and option15, then:

str00 = Compulsory option text
str01 = option04 text
str02 = option08 text
str03 = option12 text
str04 = option15 text

Now I need to generate a table based on the number of valid strings, i.e. 5, meaning that I need a 3x2 table (6 cells) and:

cell(1,1) = str00
cell(1,2) = str01
cell(2,1) = str02
cell(2,2) = str03
cell(3,1) = str04
cell(3,2) = null

Again, not sure how to do this, or if it's feasible, but with my limited understanding of vba, it's the only approach I can think of even trying!

And just to clarify my way of thinking, if the user selected option06, option07 and option23 then:

str00 = Compulsory option text
str01 = option06 text
str02 = option07 text
str03 = option23 text

In this instance I would need to generate a 2x2 table and:

cell(1,1) = str00
cell(1,2) = str01
cell(2,1) = str02
cell(2,2) = str03

Again, thanks for your time and patience.

[DKenny]

here's an example of one way to build the table on the fly. I've setup an array where I store the captions of all checked boxes on a form.
Then based on the number selected I build a table with the correct number of rows.
Then I loop through the array, passing the value of each caption to the table.

Private Sub cmdBuildTable_Click()
Dim asSelected() As String
Dim chkMyCheck As Control
Dim lRowCount As Long
Dim myRange As Range
Dim tblMyTable As Table
Dim lItem As Long
    
    'Create an array to store the selected options
    ReDim asSelected(1) As String
    
    'loop through the controls..
    For Each chkMyCheck In Me.Controls
        '..looking for checkboxes...
        If Left(chkMyCheck.Name, 3) = "chk" Then
            '...that are checked....
            If chkMyCheck.Value Then
                '....and adding them to the array
                asSelected(UBound(asSelected)) = chkMyCheck.Caption
                'Then increasing the size of the array by 1
                ReDim Preserve asSelected(UBound(asSelected) + 1) As String
            End If
        End If
    Next chkMyCheck
    
    'Make sure the array has an even number of elements by
    'removing the extra record if there are an odd number
    'of elements
    If UBound(asSelected) Mod 2 = 1 Then
        ReDim Preserve asSelected(UBound(asSelected) - 1) As String
    End If
    
    'Determine how many rows to put in the table
    lRowCount = UBound(asSelected) / 2
    
    'Add a new table, with the correct number o frows and 2 columns
    Set myRange = ActiveDocument.Range(Start:=0, End:=0)
    Set tblMyTable = ActiveDocument.Tables.Add(Range:=myRange, NumRows:=lRowCount, NumColumns:=2)
    
    'loop through the array - adding the values to the table
    For lItem = LBound(asSelected) To UBound(asSelected)
        'First we fill the first column
        If lItem <= lRowCount Then
            tblMyTable.Cell(lItem, 1).Range.InsertAfter asSelected(lItem)
        
        'then we fill the second column
        Else
            tblMyTable.Cell(lItem - lRowCount, 2).Range.InsertAfter asSelected(lItem)
        End If
    Next lItem
    
    'then close the form
    Unload Me
End Sub

[New2vba]

Thanks DKenny, that works perfectly (as you would expect).

I must admit there are many aspects of the code I have never seen before and don't fully understand, but I'm sure I'll have no problems working it out from the sample you kindly provided.

Once again, my sanity has been preserved!

EDIT

Seems I am having problems after all DKenny! I clearly overestimated my abilities!

The problem I have is that my table already exists. What I want to do is add rows and add the option text tot those rows (the pre-existing cells will be poulated using bookmarks because they will always contain something). Attached is a revised version of the sample you kindly provided, which no longer works.

It adds the correct number of rows, but because I have removed tblMytable, it no longer fills them with the option text. I know why it doesn't work, and have made numerous attempts at fixing it, but have hit a brick wall.

Private Sub cmdBuildTable_Click()
Dim asSelected() As String
Dim chkMyCheck As Control
Dim lRowCount As Long
Dim myRange As Range
Dim tblMyTable As Table
Dim lItem As Long
    
    'Create an array to store the selected options
    ReDim asSelected(1) As String
    
    'loop through the controls..
    For Each chkMyCheck In Me.Controls
        '..looking for checkboxes...
        If Left(chkMyCheck.Name, 9) = "chkOption" Then
            '...that are checked....
            If chkMyCheck.Value = True Then
                '....and adding them to the array
                asSelected(UBound(asSelected)) = chkMyCheck.Caption
                'Then increasing the size of the array by 1
                ReDim Preserve asSelected(UBound(asSelected) + 1) As String
            End If
        End If
    Next chkMyCheck
    
    'Make sure the array has an even number of elements by
    'removing the extra record if there are an odd number
    'of elements
    If UBound(asSelected) Mod 2 = 1 Then
        ReDim Preserve asSelected(UBound(asSelected) - 1) As String
    End If
    
    'Determine how many rows to put in the table
    lRowCount = UBound(asSelected) / 2
    
    'Add a new table, with the correct number of rows and 2 columns
    
'CHANGED FROM:
    'Set myRange = ActiveDocument.Range(Start:=0, End:=0)
    'Set tblMyTable = ActiveDocument.Tables.Add(Range:=myRange, NumRows:=lRowCount, NumColumns:=2)
'TO
    ActiveDocument.Tables(1).Select
    Selection.InsertRowsBelow (lRowCount)
    
    'loop through the array - adding the values to the table
    For lItem = LBound(asSelected) To UBound(asSelected) 'lItem = SIZE OF ARRAY
        'First we fill the first column
        If lItem <= lRowCount Then ''lItem LESS THAN OR EQUAL TO NUMBER OF ROWS
            [COLOR=RoyalBlue]tblMyTable.Cell(lItem, 1).Range.InsertAfter asSelected(lItem)[/COLOR]
        
        'then we fill the second column
        Else
           [COLOR=RoyalBlue]tblMyTable.Cell(lItem - lRowCount, 2).Range.InsertAfter asSelected(lItem)[/COLOR]        
    End If
    Next lItem
    
    'then close the form
    Unload Me
End Sub

Most of the code you provided is totally new to me, but I have broke it down and believe I understand the overall process.

However, I can't figure out the lines I've highlighted or the looping process in general. I've added commments to the parts I think I understand.

Any help or advice that you can offer would be appreciated.

Thanks

ANOTHER EDIT

DKenny, problem solved (I think!)

I know there are always going to be 3 rows in my document so I simply changed the code for adding option text to:

'loop through the array - adding the values to the table
    For lItem = LBound(asSelected) To UBound(asSelected)
        'First we fill the first column
        If lItem <= lRowCount Then
            ActiveDocument.Tables(1).Cell(lItem + 3, 1).Range.InsertAfter asSelected(lItem)
        
        'then we fill the second column
        Else
           ActiveDocument.Tables(1).Cell(lItem - lRowCount + 3, 2).Range.InsertAfter asSelected(lItem)
        End If
    Next lItem

I suppose the bit that was putting me off is that I have never used FOR.. NEXT and these things can sometimes seem more complicated than they are, although I still don't fully understand the statements that actually add the text and would still appreciate any feedback that you can offer.

Thanks again.

EDIT

The code had to be changed again to allow for no options being selected, which produced an error because lRowCount = 0. To resolve this I added:

If lRowCount > 0 Then
    ActiveDocument.Tables(1).Select
    Selection.InsertRowsBelow (lRowCount)
    End If