Wrestling with VBA Tables in Word and Do Loops 3

[delp300]

I am trying to create a UserForm for our customer service reps to use with a template when sending a customer a receipt.

The receipt will sometimes need multiple order lines, so I decided to use a table within Word for the order lines.

This is all being done in Word.

In order to begin figuring this out, I have decided to start simple with a 4 cell table prototype using the following:

Set myRange = ActiveDocument.Range(MoveEnd)
ActiveDocument.Tables.Add Range:=myRange, NumRows:=2, NumColumns:=2

Now, the experimental UserForm I created has two ComboBoxes: cboPubName and cboTerm.

The UserForm also has three Command Buttons: cmdAddOrder, cmdSubmit, and cmdCancel.

It is my goal to enter a Do...Loop when cmdAddOrder is clicked and exit the loop when cmdSubmit or cmdCancel is clicked.

The Do...Loop would:

1) Unload the data selected from the two Combo Boxes into the first two adjacent cells of the table
2) Move down one row on the table
3) Clear out the two Combo Boxes on the UserForm
4) Set Focus on cboPubName
5) Loop to step one

When the Submit button is clicked, it would unload whatever other controls are on the userform that don't go in the table and it would exit the Loop.

When the Cancel button is clicked, it would just cancel the whole operation.

I am having a hard time getting my head wrapped around what needs to be done here.

The main thing is how to dump the Combo Box data into the cells using the cmdAddOrder button, and how to move incrementally through the columns and rows.

I would really appreciate some guidance here. I need it in the simplest terms, as I am still learning and have only been working with VBA for about a week.

 

[genomon]

Yikes! Why use Word? Are you familiar with Access?

The early bird may get the worm, but the second mouse gets the cheese in the trap.

 

[delp300]

I am using Word because this will be a receipt which will be emailed to a customer as an attachment.

The UserForm will initialize with the opening of the Receipt template I created.

 

[SkipVought]

Yikes!

Next choice would be Excel. Word would be the LAST choice for TABULAR data, really!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[delp300]

I am using Word because I can create a nicer looking receipt in Word. I'm just not experienced enough in Excel I guess to make the receipt look the way I want it to look.

The table I am using is just a single aspect of the receipt and I haven't found a way to create a nice document in Excel.

And even if I did, I still wouldn't know how to accomplish this loop.

My final document would comprise of several Bookmarked fields, and a table of 4 columns and 3 rows. It just seems strange to do everything in Excel when it's such a small table I need.

 

[SkipVought]

Ya go with what ya know. I can understand that.

I do not understand the loop thingie, thought. You're putting ONE row of data into a table. I guess the COLUMNS in the ROW could be looped thru to assign ROW VALUES.

Maybe I do not understand the process.

Does the user select more than one item in the combo?

Is one combo based on the selection in the other?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[delp300]

I do not understand the loop thingie, thought. You're putting ONE row of data into a table. I guess the COLUMNS in the ROW could be looped thru to assign ROW VALUES.

The table exists as a way to allow the user to add more than one line item on the receipt. So if a customer orders 2 products, then two rows on the table will be used. If they order 3, then three rows would be used. And so on... The AddOrder button would exist for this purpose. If only one item was ever ordered I could just use the Submit button to Unload everything at once. But most of the time the order does only consist of one line item.

Maybe I do not understand the process.

Does the user select more than one item in the combo?

No - only one item is chosen. cboPubName consists of my company's publications. cboTerm is for the duration of the customer's subscription. So there would be nine items in cboPubName, and then the standard list of 5 different term lengths in cboTerm.

So for this simplified table, the user would select their first item from cboPubName and select the term from cboTerm and press cmdAddOrder. This would start the Do Loop, which would progress to new table Rows until the user has entered the line items.

Is one combo based on the selection in the other?

No - the items in each combo box are independent from each other. What a user chooses from cboPubName has no impact on cboTerm.


I was playing around a little while ago and I finally managed to get the ComboBox selection added to the table using this:

Private Sub cmdAddOrder_Click()
    
Do
    Selection.Tables(1).Cell(1, 1).Range.Text = cboPubName
    Selection.Tables(1).Cell(1, 2).Range.Text = cboTerm
    
            
Loop Until cmdSubmit.Enabled


End Sub

However, it's not working as I want it to. I need to figure out how to use Count to advance through the cells in the Loop.

I really appreciate the help so far. I can't wait to get this thing figured out (if it ever happens).

 

[SkipVought]

I still do not get it.

The user, very FIRST thing, enters how many items they will enter?

let's say 2.

So they enter the FIRST item.

They ADD the item to the table. That does not take a LOOP.

Sub AddRowOfData()
    Dim r As Row
    
    For Each r In Tables(1).Rows
        If Trim(Left(r.Cells(1).Range.Text, Len(r.Cells(1).Range.Text) - 2)) = "" Then
            r.Cells(1).Range.Text = "1"
            r.Cells(2).Range.Text = "2"
            Exit For
        End If
    Next

I just assigned two literals

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[delp300]

I apologize for not making myself clearer.

I'll paste my entire code so far at the bottom of this reply.

I plugged your code into my UserForm, modifying it slightly for the cmdAddOrder event and replacing the "1" and "2" values with the cboPubName and cboTerm selections.

Sub cmdAddOrder_Click()
    Dim r As Row
    
    For Each r In Tables(1).Rows
        If Trim(Left(r.Cells(1).Range.Text, Len(r.Cells(1).Range.Text) - 2)) = "" Then
            r.Cells(1).Range.Text = cboPubName
            r.Cells(2).Range.Text = cboTerm
            Exit For
        End If
    Next
    
    End Sub

When I tried running the User Form, it generated a Compile Error, stating "Sub or Function not defined".

It brought up the code and highlighted Tables in the line of code For Each r In Tables(1).Rows


Here's the code I have as it is now:

ThisDocument:

Private Sub Document_New()

Set myRange = ActiveDocument.Range(MoveEnd)
ActiveDocument.Tables.Add Range:=myRange, NumRows:=2, NumColumns:=2

UserForm1.Show

End Sub

UserForm1:

Sub cmdAddOrder_Click()
    Dim r As Row
    
    For Each r In Tables(1).Rows
        If Trim(Left(r.Cells(1).Range.Text, Len(r.Cells(1).Range.Text) - 2)) = "" Then
            r.Cells(1).Range.Text = cboPubName
            r.Cells(2).Range.Text = cboTerm
            Exit For
        End If
    Next
    
    End Sub
_________________________________________



Private Sub cmdSubmit_Click()
Unload Me
End Sub
_________________________________________

Private Sub UserForm_Initialize()

With cboPubName
    .AddItem "Publication Name 1"
    .AddItem "Publication Name 2"
    .AddItem "Publication Name 3"
    .AddItem "Publication Name 4"
End With

With cboTerm
    .AddItem "13 Weeks"
    .AddItem "26 Weeks"
    .AddItem "52 Weeks"
End With

End Sub

 

[PHV]

For Each r In [!]ActiveDocument.[/!]Tables(1).Rows

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I failed to add the document reference as PHV as supplied.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[delp300]

Hey - I think that might work for me!

It's almost the end of my day here so I'll take a break for a post-work bike ride, beer, and sleep (in that order).

Thanks for the great help. When I come in tomorrow I'll start fleshing out the example provided to suit my exact needs.

If I have trouble I'll be back and if I figure it out, I'll still be back

Thanks!

 

[fumei]

I do not understand why you have, or need, a loop at all. There does not appear to be any logical loop.

Add: take data from the textboxes and put that into the table; clear the textboxes

Submit: finish up whatever.

Where is the loop? Add performs an action, Submit performs an action.

There is a need for a counting operation. The table has two rows, so if you Add a third, obviously the table needs a new row. And so on. But other than that...where is there a loop?

Here is a possible (and since this is VBA there are other possible routes!) alternative.

In a standard module:

Option Explicit

Public counter As Long

Sub MakeTable()
Dim myRange As Range

Set myRange = ActiveDocument.Range
myRange.Collapse Direction:=wdCollapseEnd
ActiveDocument.Tables.Add _
   Range:=myRange, NumRows:=2, NumColumns:=2
ActiveDocument.Tables(1).Select
ActiveDocument.Bookmarks.Add Name:="ThisTable", _
   Range:=Selection.Range
Selection.Tables(1).Range.Cells(1).Range.Text = "Pub Name"
Selection.Tables(1).Range.Cells(2).Range.Text = "Term"

Selection.Collapse Direction:=wdCollapseEnd
counter = 2
End Sub


Sub AddToTable(strCell_1 As String, strCell_2 As String)
Dim aTable As Table
Dim aRow As Row
Dim j As Long
' set a table object for the table
Set aTable = ActiveDocument.Bookmarks("ThisTable") _
   .Range.Tables(1)
j = aTable.Rows.Count
If j = counter Then
' this only executes for the first row data
   Set aRow = aTable.Rows(counter)
   aRow.Cells(1).Range.Text = strCell_1
   aRow.Cells(2).Range.Text = strCell_2
   counter = counter + 1
Else
   aTable.Rows.Add aTable.Rows(j)
   j = aTable.Rows.Count
   aTable.Rows(j - 1).Cells(1).Range.Text = _
      CellText(aTable.Rows(j).Cells(1).Range.Text)
   aTable.Rows(j - 1).Cells(2).Range.Text = _
      CellText(aTable.Rows(j).Cells(2).Range.Text)
   
   Set aRow = aTable.Rows(j)
   aRow.Cells(1).Range.Text = strCell_1
   aRow.Cells(2).Range.Text = strCell_2
   counter = counter + 1
End If
End Sub

Function CellText(strIn As String)
   CellText = Left(strIn, Len(strIn) - 2)
End Function

In the userform module:

Option Explicit

Private Sub cmdAdd_Click()
   Call AddToTable(cboPubName, cboTerm)
   cboPubName.ListIndex = 0
   cboPubName.SetFocus
   cboTerm.ListIndex = 0
End Sub

Private Sub cmdCancel_Click()
   Unload Me
End Sub

Private Sub cmdSubmit_Click()
   ' do whatever other stuff
   Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim PubNames()
Dim Terms()
Dim var
PubNames = Array(" Please select an item ", _
   "whatever1", "whatever2", _
   "whatever3", "whatever4")
Terms = Array("Please select an item", _
   "term1", "term2", "term3", "term4")
For var = 0 To UBound(PubNames)
   cboPubName.AddItem PubNames(var)
Next
   cboPubName.ListIndex = 0
For var = 0 To UBound(Terms)
   cboTerm.AddItem Terms(var)
Next
   cboTerm.ListIndex = 0
Call MakeTable
End Sub

What it does:

1. Userform initializes and populates the comboboxes, and then calls the procedure MakeTable (in the standard module).

2. MakeTable makes a two row, two column table; selects the table; makes a bookmark of it; titles the columns with "Pub Name" and "Term" in Row 1. You did not mention titles, but what the heck.

So now there is a 2 row table like this:

Pub Name Term
< blank row >

The table is bookmarked so it is easier to make a reference to it in the AddToTable procedure.

3. User selects something from cboPubName and cboTerm and clicks Add.

4. Add_Click calls the AddToTable procedure (in the standard module), passing the values of cboPubName and cboTerm, as parameters.

5. AddToTable checks the row count. If it is 2 - ie. this is the first go-round - it sets a Row object for the empty row, and dumps in the parameters (the value of cboPubName and cboTerm). If it is not 2 then that means a new row is required.

So it adds a row. Adding a row makes the new row before the identified row. So the identified row is the LAST row. This puts a blank row ABOVE the row with the last data. So it uses the CellText function to grab that data (from the last row) and put it into the new blank row.

Then it inserts (overwrites) the last row with the new data from the comboboxes (cboPubName and cboTerm).

When AddToTable finishes, control is passed back to Add_Click. Add_Click then clears the comboboxes back to item 1 (ListIndex = 0) which is simply a "Please select".

You can keep clicking Add, and it will keep adding rows with the current data from cboPubName and cboTerm.

Clicking Submit will do whatever it is it supposed to do. I just have it unloading the userform.

Essentially, it can be summed up as:

#1. make a two row table
#2. making titles in Row 1
#3. clicking Add to take current data from the comboboxes and putting that into the blank row. If there IS no blank row, make one, and put the data in it. Details of that process described above.
#4. when you no longer want to Add, then click Submit.

Again, as far as I'm concerned there really is no loop here. Just a procedure for taking the values of the comboboxes and putting that into the table.

BTW: the line
Set myRange = ActiveDocument.Range(MoveEnd)

Are you actually getting that to do something?? I am not sure what is going on with the myRange thing.

Lastly, what EXACTLY do you want to happen when clicking Cancel? I have it that the userform starting up creates the table. So even if the user click Cancel, the table will be there. However....if you want the Cancel button to have the result there is NO table....then things have to be adjusted. Quite do-able.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

There is a problem with:

    Dim r As Row
    
    For Each r In Tables(1).Rows
        If Trim(Left(r.Cells(1).Range.Text, Len(r.Cells(1).Range.Text) - 2)) = "" Then
            r.Cells(1).Range.Text = cboPubName
            r.Cells(2).Range.Text = cboTerm
            Exit For
        End If
    Next

The table has:

blah blah
yadda yadda

For EACH Row
If cell text = "" Then
add text
End If
Next

Well....each cell DOES have text, it is NOT "".

There is no mechanism to add a row. Your table has two rows. Even if you are not adding title text to the columns, you will only ever be able to use Add twice. That is it.

Now if you only ever want to use Add twice, OK. Otherwise, you need a mechanism to keeping Adding.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

Well, I'd have a routine for clearing the table.

The OP stated a limit of 4 items.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[delp300]

Wow thanks fumei - as odd as it sounds, I can't wait to get to work tomorrow to try that out.

Yeah - my table will have a limit of 4 rows. Very rarely would anyone need to exceed those 4 and if they do, they can just revert back to the old receipt we used to use.

I'll try messing around with that code tomorrow.

I do have one other question - how can I place my table at a specified place in the Word document? It's location will be about 2/3 of the way down the page.

 

[fumei]

Stated a limit? Hmmm, Skip. I did not extrapolate:

"I have decided to start simple with a 4 cell table prototype "

to define a "limit".

Funny how assumptions are made. I assumed the OP wanted more!

faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

The 4 cell prototype isn't what I'll end up with.

The final table will most likely have 4 rows and 4 columns.

 

[SkipVought]

So add a row...

Sub AddRow()
    With ThisDocument.Tables(1)
        .Rows.Add BeforeRow:=.Rows(.Rows.Count)
    End With
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[fumei]

Of course. You add a row. However, as I pointed out, adding a row inserts a row BEFORE the identified row. So Skip's code does indeed add a row. Like mine, it inserts a row ABOVE the last row.

So if you want the data to added to next, next, next - you must move the data up.

Row 1 Row 1
Row 2 Row 2
Row 3 Row 3

When you add a row (with Skip's code, or mine) you get:

Row 1 Row 1
Row 2 Row 2
< blank new row >
Row 3 Row 3

My code does the same, and then MOVES the data (eg. Row 3 Row 3) up into the new blank row, to get:

Row 1 Row 1
Row 2 Row 2
Row 3 Row 3
Row 3 Row 3

then the second "Row 3" is overwritten with the new data from the comboboxes, to get:

Row 1 Row 1
Row 2 Row 2
Row 3 Row 3
Row 4 Row 4



faq219-2884

Gerry
My paintings and sculpture