[RESOLVED] Please help me with TAB function...

Dear Experts,

I am a newbie and I am trying to get it done and I was stack...I am creating a program....I need your helps please...

What code should I put if we put the number on my "Qty" TextBox, then when I press "TAB" and I click everywhere once...it will calculate for "Total"....

Anyone could help me for this please..???...Please have a look at my codes...

Thanks for your kind helps..

Jennifer :)

Right click on the QTY field and "Assign a Macro" to the Exit event. The macro will be your procedure to total the fields.

Hi,

Thanks for your reply...However, I make it on Microsoft Words as a VBA...I tried to follow your walktrough by doing right click on the "Qty" field, however, there was no "Assign a Micro" appear....have look into my code please..???...Because, without looking to it...it might be different possible solutions..

Thanks,

Jennifer ;)

Ok, I was talking about the Word form field qty field but here is how to do it from VBA behind the userform code.

VB Code:

Private Sub txtqty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    'Do your total code here
End Sub

Hi,

It works..!!!.. :thumb: ;)

Thanks very much for your kind helps...

Is ok, if I ask you more please..???

(I put a new attachement)...still the same file but at this time, I put additional button called "Add Rows"...which is when I click this button, there are some actions as follow:

1. It will erase all text in each TextBox within the form, then

2. it will add new row on the word documents, then

3. when I put text on each TextBox, it will write into the new rows on each bookmark on the Word Document.

Additional question is...how to put "Item Code" into the word document please..???...could I have the code please..???...because it is different code with the bookmark one....

Please help me...I am trying to create an interactive form... ;)

Thanks for your kind helps...

Best Regards,

Jennifer :)

I think this should be hat you wanted.

VB Code:

Private Sub cmdAddRow_Click()
    Dim newRow As Row
    Dim myTable As Table
    Dim celTable As Cell
    'Add row:
    Set myTable = ActiveDocument.Tables(1)
    Set newRow = myTable.Rows.Add
    'Insert UserForm textbox's text:
    celTable.Range.InsertAfter Text:=txtdes.Text
    celTable.Range.InsertAfter Text:=txtqty.Text
    celTable.Range.InsertAfter Text:=txtunit.Text
    celTable.Range.InsertAfter Text:=txttotal.Text
    'Clear textboxes:
    Me.txtunit.Text = vbNullString
    Me.txtdes.Text = vbNullString
    Me.txtqty.Text = vbNullString
    Me.txttotal.Text = vbNullString
End Sub

can you explain more on the item_code? Where are you needing to populate it from? Hardcoded values?

Hi,

Thanks for your reply...however, I found several problems here (I attached new modified file by adding your codes for your reviews):

First, when I clicked on both buttons whether "Add To Template" or "Add Rows" button, I have to drag the form itself (while running) on the Word document template to see the results/to make them appear on the Word Document... :confused: (would you like to see please..??)

Secondly, "Display The Form" on the Word Document template will be like a bug...(would you like to see please..??)

Thirdly, some of your code was not work properly:

VB Code:

'Insert UserForm textbox's text:
    celTable.Range.InsertAfter Text:=txtdes.Text
    celTable.Range.InsertAfter Text:=txtqty.Text
    celTable.Range.InsertAfter Text:=txtunit.Text
    celTable.Range.InsertAfter Text:=txttotal.Text

when I run this code, there will a debug like this:

"Run-time error '91':
Object variable or With block variable not set."

Finally, is it ok to ask the code please...???...how to delete "row" one by one (per row) from the bottom to upper...please???..I had tried to put this codes but still failed.. :confused:

VB Code:

'Delete per row variable
    Dim newRow As Row
    Dim myTable As Table
    Dim celTable As Cell
    
'Delete row:
    Set myTable = ActiveDocument.Tables(-1)
    Set newRow = myTable.Rows.Delete

I very appreciate for your kind helps...

Best Regards,

Jennifer ;)

There is another error in your total calculations. "txttotal = txtqty * txtunit" you need to multiply the numeric values by each other and not the textboxes themselves. The 91 error comes from celTable not being set, thats my mistake.

VB Code:

txttotal.Text = Val(txtqty.Text) * Val(txtunit.Text)

You will also want to validate for numeric values only so you dont get a type mismatch error if they enter a character or non-numeric values.

Hi,

Thanks....

But how to make it set please..???...

set celTable = ??????

additionally, I tried to make "Delete Rows" button, which is deleting one row per click from the bottom up...and I wrote like this but still error..why?

VB Code:

'Delete per row variable
    Dim newRow As Row
    Dim myTable As Table
    Dim celTable As Cell
    
'Delete row:
    Set myTable = ActiveDocument.Tables(-1)
    Set newRow = myTable.Rows.Delete

Please help me...

Thanks a lot.

Jennifer :)

I updated the code and attached the doc. Let me know if the Add is working correctly now. :)

Hi,

Thanks for your reply..However, there still some problem within your codes...If you dont mind, please open my attached "updated file" not the old one..please and performing test as follow (to see what is really happens there):

1. Fill the form completely, THEN
2. Click "Add To Template" button, THEN
3. Click "Add Rows" one (I have to click twice in order to create new row), THEN (after clicking twice on "Add Rows")
4. A new row appeared with a copy of the previous row, THEN
5. when I fill the form for second time, it write to the first row, not within the new row....????v :confused:

Please have a try at your convenience time and if you dont mind for the updated file....(I attached new updated file)

Thanks a lot

Jennifer ;)

I tried it and it seems to work fine for me. When I fill in the userform and click add to template it adds to the first row. Then when I click add row it add a row.

Hi,

Thanks for the reply...

Yes you were right when you click "Add Rows" it would add row one BUT when I fill new text on each TextBox. i.e. Item Code, Description, etc...it WAS NOT WRITE on the new added row (new row)...BUT it still wrote on the FIRST ROW...(all text within the TextBox on the form should be added into the new row when I click "Add to Template" for the second row)....

Are you understood what I mean? :confused:

Thanks a lot.

Jennifer :)

I dont see the need for AddTemplate? The add row command button will take the userforms textboxes and insert them into a new row. Isnt that what the goal was?
The addtemplate button will continuously add to the first row since that is where the bookmarks are.

Oooooo.....Ic.....now I am understood.... :blush:

It works...really works...!!!!..Thanks so much...!! :thumb: :wave:

Hmm...could I ask for more questions for this problem please...??? :)

Finally, what is the code to one row from the latest row (from the bottom) one by one..i.e. if I click "Delete" button...??? I had tried with this code but it was not worked:

VB Code:

Private Sub cmddelete_Click()
    Dim CancelRow As Row
    Dim myTable As Table
    'Cancel row:
    Set myTable = ActiveDocument.Tables(1)
    Set CancelRow = myTable.Rows.Delete
    'Insert UserForm textbox's text:
    CancelRow.Cells(1).Range.InsertAfter Text:=cmb_item.Text
    CancelRow.Cells(2).Range.InsertAfter Text:=txtdes.Text
    CancelRow.Cells(3).Range.InsertAfter Text:=txtqty.Text
    CancelRow.Cells(4).Range.InsertAfter Text:=txtunit.Text
    CancelRow.Cells(5).Range.InsertAfter Text:=txttotal.Text
    'Clear textboxes:
    Me.txtunit.Text = vbNullString
    Me.txtdes.Text = vbNullString
    Me.txtqty.Text = vbNullString
    Me.txttotal.Text = vbNullString
    Application.ScreenRefresh
End Sub

Lastly, how to count "Total" please...???...for an example, if I finished add 5 rows...when I click on "Total" button it will create a new table with two column, First Column said "Grand Total" and last column will calculate and show the total for the whole..???...please...??

Thanks very much for your kind helps ;)

Best Regards,

Jennifer :)

You can get the rows from the .Count property. To delete a row you need to pass the index of the row to be deleted.

VB Code:

myTable.Rows.Count 'Gives you the number of rows in the table.
 
myTable.Rows(3).Delete 'Deletes 3rd row

Hi,

I tried your codes BUT "Compile Error: Invalid use of property." and it highlighted "Count"...What should I do here..??? :confused:

Jennifer :(

This should do it. The table variable object was not set.

VB Code:

Private Sub cmddelete_Click()
    Dim myTable As Table
    'Give number of rows in the table
    Set myTable = ActiveDocument.Tables(1)
    MsgBox myTable.Rows.Count
    
    'Delete 3rd row
    myTable.Rows(3).Delete
    
End Sub

Hi,

It was error like this:

Run-Time Error '5941': The requested member of the collection does not exist.

What I am trying to do is, if everytime I click "Delete Row", it will erase the current row...then previous row...then previous row...and so on...For an example: If I want to delete current row, it will deleted....OR...if I want to delete 3 or 4 or 7 ,etc rows from the bottom it also can do it (i.e. if want to delete 7 rows from the bottom...I should clicking 7 times "Delete Rows"....

In order to do this, what codes should I put in please..????... :(



Thanks a lot...

Jennifer ;)

Oh thats because when you delete the index number no longer exists and the collection should get reorded. You need to delete backwards in a reverse loop stepping by a -1. Delete the last item then the next, etc. Delte row 5 of 5. Then delete row 4 for a now updated 4, etc.

Hi,

Sorry...I tried many times, but I got stack...I am really newbie about this...would you please give me an example for this please...???...I put FOR...NEXT statement between "myTable.Rows(a-1).Delete" or before...I have no idea...unless you provide similar example.......Sorry, I shame about this...but I am newbie for coding and try to learn from what you gave to me... :blush:

Jennifer :confused:

VB Code:

Set myTable = ActiveDocument.Tables(1)
Dim i as integer
For i = myTable.Rows.Count to 1 Step -1
    myTable.Rows(i).Delete
Next

This will delete each and every row. Is that what your looking for?

Hi,

Thanks a lot again...

However, when I use your last codes, it will erase all rows...what I need is delete for each row...Hmmmm...ok, like this...last night I was learning your 2 codes....

VB Code:

Dim myTable As Table
'Give number of rows in the table
Set myTable = ActiveDocument.Tables(1)
    
MsgBox myTable.Rows.Count
 
'Delete 3rd row
myTable.Rows(2).Delete

AND

VB Code:

Set myTable = ActiveDocument.Tables(1)
Dim i as integer
For i = myTable.Rows.Count to 1 Step -1
    myTable.Rows(i).Delete
Next

I think after I tried your codes, your first code is really work for me ...BUT ONLY when it is delete on the 2 row..it will debug/error...Do you know how to make it when the last row was not available for deleting, it will show a message "There is no more row to delete." instead of a debug dialog open..please..???..How to express it..???...Because if I use IF STATEMENT it compiled error...like this:

VB Code:

Dim myTable As Table
 
'Give number of rows in the table
if (Set myTable = ActiveDocument.Tables(1)) Then
    
MsgBox myTable.Rows.Count
 
'Delete 3rd row
myTable.Rows(2).Delete
 
else
 
Msg "No more row to delete."

How to do the correct order please..?? :confused:

Thanks a lot.

Jennifer ;)

Oh I see. To delete a single row for each button click it would be like this for the deletion of the last row each click.

VB Code:

Set myTable = ActiveDocument.Tables(1)
If myTable.Rows.Count > 1 Then
    myTable.Rows(myTable.Rows.Count)
Else
    MsgBox "No more rows to delete!"
End If

Hi,

Good morning... :)

I have tried your last code but when I run the code and "Delete Row" it was:

"Compile Error: Invalid Use of Property." and it highlighted on "Rows"...what should I do then..??

Jennifer :wave:

Do you have any Rows in your first table?

Hi,

Yes, I have 3 rows...But when I would like start to delete one row using your last code, it said:


"Compile Error: Invalid Use of Property." and it highlighted on "Rows"...what should I do then..??


Hmmm...here is the attachement with your new codes...if you would like to have a look...

Thanks,

Jennifer :confused:

You forgot the Delete part. :)

VB Code:

myTable.Rows(myTable.Rows.Count)[b].Delete[/b]

Hi,

It works..!!!..Thanks a lot.. :wave:

Hmmm...finally, do you know the codes to calculate Grandtotal from all "Total" TextBoxs please..?? For instance, after I finished to add rows, when I click "Calculate Total" it will create one row with two columns only, first column is "Grandtotal" and another column is its calculation.... :confused:

Thanks very much.

Jennifer :)

Yes, thats not too hard. Just loop through the .Rows collection adding the Cells(5) cell to a variable for each row.

Hi,

I tried to make its codes for total for almost 4 hours but I got stress because none of them are working...would you please let me know its code please so I can learn from there if you dont mind..?? :confused:

In addition, how to learn the logic like you said to me interpreting to VB language please..?? :) because I know what you said BUT when I tried to translate to VB codes, it was frustrated me..

Thanks a lot,

Jennifer ;)

Here is how to total up one column in your table.

VB Code:

Dim MyTotal As Single
Set myTable = ActiveDocument.Tables(1)
Dim i As Integer
For i = 2 To myTable.Rows.Count '2 because you dont want to total the header
    myTotal = MyTotal + myTable.Rows(i).Cells(5)
Next

Hi,

I tried many times to think how to works but why it always "Type Mismatch." and highlighted "Cells".. :confused:

Its because there is a trailing chr 13 and chr 7. We strip that out and it works. Sorry, I should have tested it.

VB Code:

Dim MyTotal As Single
    Dim myCel As Cell
    Set myTable = ActiveDocument.Tables(1)
    Dim i As Integer
    For i = 2 To myTable.Rows.Count '2 because you dont want to total the header
        Set myCel = myTable.Rows(i).Cells.Item(5)
        MyTotal = MyTotal + CSng(Replace(myCel.Range.Text, Chr(13) & Chr(7), ""))
    Next

Hi,

When I tried your codes for the first time, the variable of "myTable" was not defined as a pop up message coming up...then I declare it "Dim myTable As Table"....but what happen was, when I clicked on the "Total" button it was nothing happens....

:confused:

Jennifer

Do you have numeric values in your 5th column?

Hi,

Thanks for your fast reply..

Yes, it has numerical values...please have a look to my attachement...I would like to add those total, ...but nothing happens.. :confused:

Its working correctly. You just need to set the variable MyTotal to what or where ever you want it to display.

Hi,

What do you mean by set variable to what ever I want to display..??..

I have already set MyTotal variable as follow:

VB Code:

Dim MyTotal As Single

Jennifer :confused:

Like ...

VB Code:

MsgBox "Total: " & MyTotal

VB Code:

Private Sub cmdtotal_Click()
 
    Dim MyTotal As Single
    Dim myTable As Table
    Dim myCel As Cell
    Set myTable = ActiveDocument.Tables(1)
    Dim i As Integer
    For i = 2 To myTable.Rows.Count '2 because you dont want to total the header
        Set myCel = myTable.Rows(i).Cells.Item(5)
        MyTotal = MyTotal + CSng(Replace(myCel.Range.Text, Chr(13) & Chr(7), ""))
    Next
    'Just as an example of how to display the total.
    MsgBox "Total: " & MyTotal
 
End Sub

Hi,

It is works...But, why it did not doing anything (I mean calculate its total) until I move the form..???...In short, why I have to remove the form to get the total..???..Would you please have a look into my current updated file please which is I attached for you for your review...??? :)

Thanks a lot for your helps...

Jennifer ;)

Try a "Application.ScreenRefresh" after the total calculation. I couldnt get your latest version to actually run for some reason.

Hi,

I had tried this but still did not work.... :confused:

VB Code:

Private Sub cmdtotal_Click()
 
    Dim MyTotal As Single
    Dim myTable As Table
    Dim myCel As Cell
    Set myTable = ActiveDocument.Tables(1)
    Dim i As Integer
    For i = 2 To myTable.Rows.Count '2 because you dont want to total the header
        Set myCel = myTable.Rows(i).Cells.Item(5)
        MyTotal = MyTotal + CSng(Replace(myCel.Range.Text, Chr(13) & Chr(7), ""))
    Next
    
    Application.ScreenRefresh
    
    ActiveDocument.FormFields("bkTotal").Result = MyTotal
 
End Sub

You have it backwards. Should be refreshing after the total is applied.

VB Code:

Private Sub cmdtotal_Click()
 
    Dim MyTotal As Single
    Dim myTable As Table
    Dim myCel As Cell
    Set myTable = ActiveDocument.Tables(1)
    Dim i As Integer
    For i = 2 To myTable.Rows.Count '2 because you dont want to total the header
        Set myCel = myTable.Rows(i).Cells.Item(5)
        MyTotal = MyTotal + CSng(Replace(myCel.Range.Text, Chr(13) & Chr(7), ""))
    Next
    
    ActiveDocument.FormFields("bkTotal").Result = MyTotal    
    
    Application.ScreenRefresh
 
End Sub

Hi,

It works..!!!...thanks a lot a lot a lot for your kind fully helps ;) :thumb:

I just wanna ask...why the file bit changed without action taken...Usually, when I open this file I can click "Display The Form" button on the word document template...But, now why when I click on this button the mouse icon did not changed into an arrow (which is ready to click sign)..???...Could you help for this please...???..

Please have a look into my finished file....(I have attached a file for your review).. :)

Thanks a lot,

Jennifer ;)

Yes, that hte issue I had with your previous attachment. Not sure why it wont get out of design mode.

Hi,

Thank you very much anyway for your kind helps...if you are not sure..I will ask in a new thread....is that all right..???

In addition, is that all right to post this issues and problem in the TUTORIAL so, everybody can learn how to make interactive invoice using VBA Words..???

Please let me know for this.... :)

Thank you so much for your kind helps....

All the best for you!!!

Jennifer ;)

Your Welcome :)

Basically the Tutorials section would require a reformatting of the concerned post(s) but yes if you'd like.

Yes, you can ask this new issue in a new thread to keep things segragated.

Ps, dont forget to 'Resolve' this thread then if your going to start the new issue in a new thread. ;)

How to put 'RESOLVE' to this thread..???

Go to the Thread Tools menu when viewing your thread and click the "Mark thread as Resolved". ;) :)