VBA userform - using amounts entered in textboxes to calculate tot

[mgirving]

I have set up a userform in Visual Basic with two textboxes that the user
enters two amounts into (amount1 and amount2). Is it possible in the Word
template to then write a formula that displays the total of those two
amounts. At the moment I have set up a table in the template that is showing
amount 1, amount 2 and then has a formula in the right column that is a
simple sumleft formula. I would prefer to not even have amount 1 and amount
2 show up as the reader of the ultimate document doesn't need to see those
amounts, just the total. Can the VB textboxes be used as bookmarks for
example from which I can then write a formula based on the bookmarks? Thank
you for any assistance.

 

[Greg Maxey]

You don't need to use bookmarks to write a formula, you can do it directly
in the Userform.

Something like this in a commandbutton click event should work:

ActiveDocument.Tables(1).Cell(1, 3).Range.Text = CSng(Me.TextBox1) +
CSng(Me.TextBox2)

 

[mgirving]

Thank you Greg. When I run that I get the following error:

Runtime 5941 The requested member of the collection does not exist.

I would be grateful if you could guide me as to what I might be doing wrong?

I would also like to be able to write next to the total that is generated
the word Total. How can I do that in the table that is generated?

Many thanks for your guidance.

 

[Graham Mayor]

If the text boxes on your user form are Textbox1 and TextBox2 and you have
the requisite cell (row 1 column 3) in your table then

Private Sub CommandButton1_Click()
Me.Hide
ActiveDocument.Tables(1).Cell(1, 3).Range.Text = "Total " _
& CSng(Me.TextBox1) + CSng(Me.TextBox2)
End Sub

will write Total, plus the sum of the contents of the two textboxes in cell
row1 col 3 when you click CommandButton_1


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Greg Maxey]

I think Graham has already addressed the problem and answered your
additional question. The requested member of the collection that doesn't
exist is either Table 1 (the first table) in the document or a Cell located
at row 1 column 3. I assumed that your document already had a table.

 

[mgirving]

Thank you to both Greg and Graham. Both of your answers did the trick. I
would like to finetune it with the following if it is possible:

1. If a user leaves a textbox blank it seems to error out. Is there a way
of it still running where a textbox is left blank?

2. I would like the totals that are displayed in the table to be in
currency format.

Thank you for any suggestions.

 

[Greg Maxey]

Private Sub CommandButton1_Click()
Dim x As Single
Dim y As Single
On Error Resume Next
x = CSng(Me.TextBox1)
y = CSng(Me.TextBox2)
ActiveDocument.Tables(1).Cell(1, 3).Range.Text = Format(x + y, "$#,#.00")
Unload Me
End Sub