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.

 

[delp300]

Okay. Thanks to everyone's help I seem to be on the right track. I really appreciate all the assistance. I didn't think this project would get over my head so quickly.

I incorporated my Receipt template with fumei's long length of code he provided. The only problem is that when I bring up the template to test it, the table appears at the bottom of the template.

Is there a way to define where the table would be inserted? It's ideal location would be about halfway down the page, between some other text.

 

[fumei]

Is there a way to define where the table would be inserted? It's ideal location would be about halfway down the page, between some other text.

Yes, of course there is.

You can put it where ever you want to put it. You did not specify this. Your OP used that myRange thing...which I still think would not work. However, as it stands, it appears to put the table at the end of the document.

How do you put it at a specific location? By defining a specific location. A bookmark would be handy.

"It's ideal location would be about halfway down the page, between some other text."

That does not tell me anything.

Mark a location, and then put the table there.

If this is coming from a template (and do you mean a REAL template, a .dot file?), why not simply have the table there in the first place? Why are you creating the table?

Question: are you using proper styles?
Question: if this is a real template, I would assume that you are using the table creation process ONCE. Yes?

In any case, to put the table at a specific location, make a bookmark AT that location.

1. go to the location you want
2. Insert > Bookmarks, give it name (say TableHere), and click OK.

Now when you run the MakeTable procedure, use the range of the bookmark.

ActiveDocument.Tables.Add _
   Range:=ActiveDocument.Bookmarks("TableHere").Range, _
   NumRows:=2, NumColumns:=2

faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

Thank you - that worked perfectly.

I apologize for causing so much confusion. I am not familiar with a lot of terms yet.

 

[fumei]

That's OK. Hopefully you have it worked out now.

faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

I'm sorry to keep coming back with problems. I really appreciate all the help I've gotten with this but it's gotten to a point where I'm completely lost. I didn't intend for this project to become such a headache. I'm just ready to be done with this so I can go back to programming Hello World statements


I've uploaded my Word template and it should be attached to this post.

I'm also including a screenshot of the form I am working with (bottom of post).

I tweaked the code to suit my needs (as best I could) but now I am getting an error when I try the AddOrder button.

"Compile error: Argument not optional"

It highlights: Call AddToTable in the following code:

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

This is the Module1 code:

Option Explicit

Public counter As Long

Sub MakeTable()
Dim myRange As Range

Set myRange = ActiveDocument.Range
myRange.Collapse Direction:=wdCollapseEnd
ActiveDocument.Tables.Add _
   Range:=ActiveDocument.Bookmarks("Table").Range, NumRows:=4, NumColumns:=4
ActiveDocument.Tables(1).Select
ActiveDocument.Bookmarks.Add Name:="ThisTable", _
   Range:=Selection.Range


Selection.Collapse Direction:=wdCollapseEnd
counter = 4

End Sub


Sub AddToTable(strCell_1 As String, strCell_2 As String, strCell_3 As String, strCell_4 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
   aRow.Cells(3).Range.Text = strCell_3
   aRow.Cells(4).Range.Text = strCell_4
   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)
   aTable.Rows(j - 1).Cells(3).Range.Text = _
      CellText(aTable.Rows(j).Cells(3).Range.Text)
   aTable.Rows(j - 1).Cells(4).Range.Text = _
      CellText(aTable.Rows(j).Cells(4).Range.Text)
   
   Set aRow = aTable.Rows(j)
   aRow.Cells(1).Range.Text = strCell_1
   aRow.Cells(2).Range.Text = strCell_2
   aRow.Cells(3).Range.Text = strCell_3
   aRow.Cells(4).Range.Text = strCell_4
   
   counter = counter + 1
End If
End Sub

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

And this is the frmReceipt code (UserForm)

Option Explicit

Private Sub cmdAddOrder_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
   With ActiveDocument
    
    .Bookmarks("Name").Range.Text = txtName.Value
    .Bookmarks("Address").Range.Text = txtAddress.Value
    .Bookmarks("AccountNumber").Range.Text = txtAccountNumber.Value
    .Bookmarks("CardType").Range.Text = cboCardType.Value
   End With
   Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim PubNames()
Dim Terms()
Dim CardType()
Dim var
PubNames = Array("Select Publication", _
   "Air Force Times", "Army Times", _
   "Marine Corps Times", "Navy Times", _
   "Defense News", "Federal Times", _
   "Armed Forces Journal", "TSJ", _
   "C4ISR Journal")
Terms = Array("Select the Term", _
   "13 Weeks", "26 Weeks", "52 Weeks", "104 Weeks", "156 Weeks")
CardType = Array("Select Payment Type", _
    "Visa", "Mastercard", "American Express", "Discover")

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

And on ThisDocument I've got a simple thing to pull up the form:

Private Sub Document_New()
frmReceipt.Show
End Sub

Word template:

http://www.megaupload.com/?d=Y6PC089I

UserForm screenshot:

http://img142.imageshack.us/img142/1509/screenshot00289as4.gif

 

[SkipVought]

""Compile error: Argument not optional"

It highlights: Call AddToTable ..."

Did you happen to look at HELP for the AddToTable Method?

Skip,

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

 

[SkipVought]

oops, missed that one!

AddToTable takes two STRING arguments. You just included the OBJECT, rather than the text property.

Skip,

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

 

[fumei]

Skip is correct.

AddToTable calls for two string arguments.

Sub AddToTable(strCell_1 As String, strCell_2 As String)

So using Call, you need:

Call AddToTable(somestring, somestring)

However, interesting, as it does work for me just using cboPubName and cboTerm, as the default value of that is the .Value, which is also a string value.

Still, it is better to be specific. So change it to:

Private Sub cmdAddOrder_Click()
   Call AddToTable(cboPubName[b].Text[/b], cboTerm[b].Text[/b])
   cboPubName.ListIndex = 0
   cboPubName.SetFocus
   cboTerm.ListIndex = 0
End Sub

That will pass an explicit string parameter, as required.


faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

Thanks a ton - I will try that when I get back to work

 

[delp300]

By the way, can anyone recommend a good book on VBA?

 

[fumei]

1. Use Help. While some things are convoluted and obscure, quite a lot of it is very useful. Learn to press that F1 key.

2. Use the Object Browser. You can learn a lot from it.

3. Study the object model of whatever application you are working with.

4. While a little out-of-date, VBA Developer's Handbook, by Ken Getz and Mike Gibert (published by Sybex) is a huge resource. You may be able to find it in a second hand computer book store.

5. Also a little out-of-date, but another huge resource, is Word 2000 Developer's Handbook, by Guy Hart-Davis (also published by Sybex). 95%+ of it is still very applicable for Word up to 2003. But not 2007.

faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

Thanks for all the help and the tips fumei. I am very close to being done. The AddOrder button is working and everything is being added to the receipt from the form.

For some reason the AddOrder button is dumping everything into the last row of the table, instead of the first, but I will look into fixing that.

 

[delp300]

Actually now that I've taken a closer look at it, I see that each click of the AddOrder button is adding new rows on the bottom of the table, rather than using the existing cells.

 

[fumei]

For some reason the AddOrder button is dumping everything into the last row of the table, instead of the first, but I will look into fixing that.

It is supposed to! If I may remind you...

From your OP:

"2) Move down one row on the table"

That sounds like incrementing downwards. You never mentioned you wanting new rows being "first".

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

The best way to order a table is to add at the bottom and then SORT. INSERT often gets messy.

Skip,

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

 

[fumei]

There has been no mention of sorting anything.

faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

It is supposed to! If I may remind you...
From your OP:

"2) Move down one row on the table"

That sounds like incrementing downwards. You never mentioned you wanting new rows being "first".

Sorry for the confusion.

What I had in mind was that I would have a 4 row, 4 column table created. The AddOrder button would just add the input from its four associated fields into the first available row on the table. After that row, it would move down to the next available row.

It looks like a table is being created initially, but instead of filling the empty rows, the AddOrder button is just adding new rows to the end of the existing table.

I guess my terminology isn't up to par

 

[fumei]

Sorry, but I am finding this exhausting. You need to learn to fully think out, and state, your logic.

What does "next available" mean?

You have a four row table. I assume it is empty, the rows have nothing in them.

User clicks Add. The FIRST row has the contents of the comboboxes put there.

User clicks Add. The SECOND row has the contents of the comboboxes put there.

User clicks Add. The THIRD row has the contents of the comboboxes put there.

User clicks Add. The FOURTH row has the contents of the comboboxes put there.

User clicks Add. Ummmmmmmmm, now what?

As for it not working, then you probably changed something. I notice that your AddToTable:

Sub AddToTable(strCell_1 As String, strCell_2 As String, strCell_3 As String, strCell_4 As String)

has four parameters.

When you call it you must give it four parameters.

Call AddToTable(cboPubName.Text, cboTerm.Text)

would be only two.

It looks like a table is being created initially, but instead of filling the empty rows, the AddOrder button is just adding new rows to the end of the existing table.

Are you saying it is adding NO TEXT at all?


faq219-2884

Gerry
My paintings and sculpture

 

[delp300]

I'm not sure what would happen on the fifth click of the AddOrder button. As I said, this project quickly got way over my head as far as my abilities and understanding are concerned.

As far as the extra rows being created with each click of the AddOrder button - yes, it is adding the correct text in the cells, but in newly created rows at the end of the existing 4 row table. I'm not sure how to state it otherwise.

I'm really sorry this has gotten so out of hand - I thought this was going to be a small project. Feel free to let this thread die out.

 

[fumei]

I have no time to recreate your table, but essentially this is the purpose of a counter.

The original table has 4 rows. Count the number of times you are adding to the table with the Add button. If it is the first time, put the stuff into row 1, the second time, into row 2....

The fifth time - and from now on - add a new row first.

And as I explained, you add a new row, and have to MOVE the existing last row contents there, THEN add the next stuff into the last row. Adding a row programatically uses BeforeRow.



faq219-2884

Gerry
My paintings and sculpture