ExcelBanter - code to sum 8 textboxes

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - code to sum 8 textboxes (https://www.excelbanter.com/excel-programming/436225-code-sum-8-textboxes.html)

code to sum 8 textboxes

Hi there,
I am trying to get a text box (txtsubtotal) to sum 8 other 'price' text
boxes (txtprice1...txtprice8) when a command button (cmdCalculate) is
clicked. Each 'price' text box is filled via a combo box (cboitem1) change
code.
Here is briefly what I have:

Each combo box is coded like this:
Private Sub cboItem1_change()
Me.txtprice1 = WorksheetFunction.VLookup(Me.cboItem1, Worksheets
_("Pizzas").Range("A:B"), 2, 0)
End Sub

Each 'price' text box is coded like this:
Private Sub txtprice1_change()
txtprice1.Value = Format(Me.txtprice1.Value, "$#,##0.00")
End Sub

And the command button click is:
Private Sub cmdCalculate_Click()
If IsNumeric(Me.txtprice1.Value) _
And IsNumeric(Me.txtprice2.Value) _
And IsNumeric(Me.txtprice3.Value) _
And IsNumeric(Me.txtprice4.Value) _
And IsNumeric(Me.txtprice5.Value) _
And IsNumeric(Me.txtprice6.Value) _
And IsNumeric(Me.txtprice7.Value) _
And IsNumeric(Me.txtprice8.Value) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Me.txtprice2.Value) _
+ CDbl(Me.txtprice3.Value) _
+ CDbl(Me.txtprice4.Value) _
+ CDbl(Me.txtprice5.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice7.Value) _
+ CDbl(Me.txtprice8.Value)
End If
End Sub

However it seems to work only sometimes. At the moment when I click
cmdCalculate when the form is 'live' nothing happens at all. It has worked
before though. It seems when I get a run-time error on any different
unrelated code it stuffs this one up.

What am I missing?

Thanks in advance :)

code to sum 8 textboxes

Try these changes. I added Trim() method and added a message box to indicate
when the data is not numeric to help isolate the problem.

Private Sub cmdCalculate_Click()
If IsNumeric(Trim(Me.txtprice1.Value)) _
And TrimIsNumeric(Me.txtprice2.Value)) _
And TrimIsNumeric(Me.txtprice3.Value)) _
And Trim(IsNumeric(Me.txtprice4.Value)) _
And Trim(IsNumeric(Me.txtprice5.Value)) _
And Trim(IsNumeric(Me.txtprice6.Value)) _
And Trim(IsNumeric(Me.txtprice7.Value)) _
And Trim(IsNumeric(Me.txtprice8.Value)) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Trim(Me.txtprice2.Value)) _
+ CDbl(Trim(Me.txtprice3.Value)) _
+ CDbl(Trim(Me.txtprice4.Value)) _
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/ex...tml#post559813
+ CDbl(Trim(Me.txtprice5.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice7.Value)) _
+ CDbl(Trim(Me.txtprice8.Value))
Else
msgbox("Amounts are not Numbers")
End If
End Sub

"Rachel" wrote:

Hi there,
I am trying to get a text box (txtsubtotal) to sum 8 other 'price' text
boxes (txtprice1...txtprice8) when a command button (cmdCalculate) is
clicked. Each 'price' text box is filled via a combo box (cboitem1) change
code.
Here is briefly what I have:

Each combo box is coded like this:
Private Sub cboItem1_change()
Me.txtprice1 = WorksheetFunction.VLookup(Me.cboItem1, Worksheets
_("Pizzas").Range("A:B"), 2, 0)
End Sub

Each 'price' text box is coded like this:
Private Sub txtprice1_change()
txtprice1.Value = Format(Me.txtprice1.Value, "$#,##0.00")
End Sub

And the command button click is:
Private Sub cmdCalculate_Click()
If IsNumeric(Me.txtprice1.Value) _
And IsNumeric(Me.txtprice2.Value) _
And IsNumeric(Me.txtprice3.Value) _
And IsNumeric(Me.txtprice4.Value) _
And IsNumeric(Me.txtprice5.Value) _
And IsNumeric(Me.txtprice6.Value) _
And IsNumeric(Me.txtprice7.Value) _
And IsNumeric(Me.txtprice8.Value) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Me.txtprice2.Value) _
+ CDbl(Me.txtprice3.Value) _
+ CDbl(Me.txtprice4.Value) _
+ CDbl(Me.txtprice5.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice7.Value) _
+ CDbl(Me.txtprice8.Value)
End If
End Sub

However it seems to work only sometimes. At the moment when I click
cmdCalculate when the form is 'live' nothing happens at all. It has worked
before though. It seems when I get a run-time error on any different
unrelated code it stuffs this one up.

What am I missing?

Thanks in advance :)

code to sum 8 textboxes

Try this

Private Sub cmdCalculate_Click()
Dim txt As Control
X = 0
For Each txt In Me.Controls
If TypeName(txt) = "TextBox" Then
If txt.Value < "" Then Y = CInt(txt.Value) + Y
X = X + 1
End If
If X = 2 Then Exit For
Next txt
Me.txtSubTotal.Value = Y
End Sub

On Nov 16, 5:59*pm, Rachel wrote:
Hi there,
I am trying to get a text box (txtsubtotal) to sum 8 other 'price' text
boxes (txtprice1...txtprice8) when a command button (cmdCalculate) is
clicked. Each 'price' text box is filled via a combo box (cboitem1) change
code.
Here is briefly what I have:

Each combo box is coded like this:
Private Sub cboItem1_change()
Me.txtprice1 = WorksheetFunction.VLookup(Me.cboItem1, Worksheets
_("Pizzas").Range("A:B"), 2, 0)
End Sub

Each 'price' text box is coded like this:
Private Sub txtprice1_change()
txtprice1.Value = Format(Me.txtprice1.Value, "$#,##0.00")
End Sub

And the command button click is:
Private Sub cmdCalculate_Click()
If IsNumeric(Me.txtprice1.Value) _
*And IsNumeric(Me.txtprice2.Value) _
*And IsNumeric(Me.txtprice3.Value) _
*And IsNumeric(Me.txtprice4.Value) _
*And IsNumeric(Me.txtprice5.Value) _
*And IsNumeric(Me.txtprice6.Value) _
*And IsNumeric(Me.txtprice7.Value) _
*And IsNumeric(Me.txtprice8.Value) Then
* *Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice2.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice3.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice4.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice5.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice6.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice6.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice7.Value) _
* * * * * * * * * * * * * * * *+ CDbl(Me.txtprice8.Value)
End If
End Sub

However it seems to work only sometimes. At the moment when I click
cmdCalculate when the form is 'live' nothing happens at all. It has worked
before though. It seems when I get a run-time error on any different
unrelated code it stuffs this one up.

What am I missing?

Thanks in advance :)

code to sum 8 textboxes

Excellent Joel, seems to be working perfectly.
One little change, not all the text boxes will need to be completed all the
time eg only txtprice1 through txtprice4 may be completed if there are only 4
items. At the moment the cmdcalculate won't calculate because the empty cells
aren't a number (msgbox appears).
Is it possible to allow them to be blank?
I have also tried a different work around where in userform_initialise I
have set the value of these txtboxes to "$0.00" which then allows the
calculation however this will also then be transferred to the worksheet with
cmdAdd which is just going to get really messy......

Thanks a million for you prompt help :)

"Joel" wrote:

Try these changes. I added Trim() method and added a message box to indicate
when the data is not numeric to help isolate the problem.

Private Sub cmdCalculate_Click()
If IsNumeric(Trim(Me.txtprice1.Value)) _
And TrimIsNumeric(Me.txtprice2.Value)) _
And TrimIsNumeric(Me.txtprice3.Value)) _
And Trim(IsNumeric(Me.txtprice4.Value)) _
And Trim(IsNumeric(Me.txtprice5.Value)) _
And Trim(IsNumeric(Me.txtprice6.Value)) _
And Trim(IsNumeric(Me.txtprice7.Value)) _
And Trim(IsNumeric(Me.txtprice8.Value)) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Trim(Me.txtprice2.Value)) _
+ CDbl(Trim(Me.txtprice3.Value)) _
+ CDbl(Trim(Me.txtprice4.Value)) _
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/ex...tml#post559813
+ CDbl(Trim(Me.txtprice5.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice7.Value)) _
+ CDbl(Trim(Me.txtprice8.Value))
Else
msgbox("Amounts are not Numbers")
End If
End Sub

"Rachel" wrote:

Hi there,
I am trying to get a text box (txtsubtotal) to sum 8 other 'price' text
boxes (txtprice1...txtprice8) when a command button (cmdCalculate) is
clicked. Each 'price' text box is filled via a combo box (cboitem1) change
code.
Here is briefly what I have:

Each combo box is coded like this:
Private Sub cboItem1_change()
Me.txtprice1 = WorksheetFunction.VLookup(Me.cboItem1, Worksheets
_("Pizzas").Range("A:B"), 2, 0)
End Sub

Each 'price' text box is coded like this:
Private Sub txtprice1_change()
txtprice1.Value = Format(Me.txtprice1.Value, "$#,##0.00")
End Sub

And the command button click is:
Private Sub cmdCalculate_Click()
If IsNumeric(Me.txtprice1.Value) _
And IsNumeric(Me.txtprice2.Value) _
And IsNumeric(Me.txtprice3.Value) _
And IsNumeric(Me.txtprice4.Value) _
And IsNumeric(Me.txtprice5.Value) _
And IsNumeric(Me.txtprice6.Value) _
And IsNumeric(Me.txtprice7.Value) _
And IsNumeric(Me.txtprice8.Value) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Me.txtprice2.Value) _
+ CDbl(Me.txtprice3.Value) _
+ CDbl(Me.txtprice4.Value) _
+ CDbl(Me.txtprice5.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice7.Value) _
+ CDbl(Me.txtprice8.Value)
End If
End Sub

However it seems to work only sometimes. At the moment when I click
cmdCalculate when the form is 'live' nothing happens at all. It has worked
before though. It seems when I get a run-time error on any different
unrelated code it stuffs this one up.

What am I missing?

Thanks in advance :)

code to sum 8 textboxes

Try this

Private Sub cmdCalculate_Click()

Dim MyTotal As Double
MyTotal = 0
If IsNumeric(Trim(Me.txtprice1.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice1.Value)
End If
If IsNumeric(Trim(Me.txtprice2.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice2.Value)
End If
If IsNumeric(Trim(Me.txtprice3.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice3.Value)
End If
If IsNumeric(Trim(Me.txtprice4.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice4.Value)
End If
If IsNumeric(Trim(Me.txtprice5.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice5.Value)
End If
If IsNumeric(Trim(Me.txtprice6.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice6.Value)
End If
If IsNumeric(Trim(Me.txtprice7.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice7.Value)
End If
If IsNumeric(Trim(Me.txtprice8.Value)) Then
MyTotal = MyTotal + Trim(Me.txtprice8.Value)
End If

Me.txtSubTotal.Value = MyTotal

End Sub

"Rachel" wrote:

Excellent Joel, seems to be working perfectly.
One little change, not all the text boxes will need to be completed all the
time eg only txtprice1 through txtprice4 may be completed if there are only 4
items. At the moment the cmdcalculate won't calculate because the empty cells
aren't a number (msgbox appears).
Is it possible to allow them to be blank?
I have also tried a different work around where in userform_initialise I
have set the value of these txtboxes to "$0.00" which then allows the
calculation however this will also then be transferred to the worksheet with
cmdAdd which is just going to get really messy......

Thanks a million for you prompt help :)

"Joel" wrote:

Try these changes. I added Trim() method and added a message box to indicate
when the data is not numeric to help isolate the problem.

Private Sub cmdCalculate_Click()
If IsNumeric(Trim(Me.txtprice1.Value)) _
And TrimIsNumeric(Me.txtprice2.Value)) _
And TrimIsNumeric(Me.txtprice3.Value)) _
And Trim(IsNumeric(Me.txtprice4.Value)) _
And Trim(IsNumeric(Me.txtprice5.Value)) _
And Trim(IsNumeric(Me.txtprice6.Value)) _
And Trim(IsNumeric(Me.txtprice7.Value)) _
And Trim(IsNumeric(Me.txtprice8.Value)) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Trim(Me.txtprice2.Value)) _
+ CDbl(Trim(Me.txtprice3.Value)) _
+ CDbl(Trim(Me.txtprice4.Value)) _
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/ex...tml#post559813
+ CDbl(Trim(Me.txtprice5.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice6.Value)) _
+ CDbl(Trim(Me.txtprice7.Value)) _
+ CDbl(Trim(Me.txtprice8.Value))
Else
msgbox("Amounts are not Numbers")
End If
End Sub

"Rachel" wrote:

Hi there,
I am trying to get a text box (txtsubtotal) to sum 8 other 'price' text
boxes (txtprice1...txtprice8) when a command button (cmdCalculate) is
clicked. Each 'price' text box is filled via a combo box (cboitem1) change
code.
Here is briefly what I have:

Each combo box is coded like this:
Private Sub cboItem1_change()
Me.txtprice1 = WorksheetFunction.VLookup(Me.cboItem1, Worksheets
_("Pizzas").Range("A:B"), 2, 0)
End Sub

Each 'price' text box is coded like this:
Private Sub txtprice1_change()
txtprice1.Value = Format(Me.txtprice1.Value, "$#,##0.00")
End Sub

And the command button click is:
Private Sub cmdCalculate_Click()
If IsNumeric(Me.txtprice1.Value) _
And IsNumeric(Me.txtprice2.Value) _
And IsNumeric(Me.txtprice3.Value) _
And IsNumeric(Me.txtprice4.Value) _
And IsNumeric(Me.txtprice5.Value) _
And IsNumeric(Me.txtprice6.Value) _
And IsNumeric(Me.txtprice7.Value) _
And IsNumeric(Me.txtprice8.Value) Then
Me.txtSubTotal.Value = CDbl(Me.txtprice1.Value) _
+ CDbl(Me.txtprice2.Value) _
+ CDbl(Me.txtprice3.Value) _
+ CDbl(Me.txtprice4.Value) _
+ CDbl(Me.txtprice5.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice6.Value) _
+ CDbl(Me.txtprice7.Value) _
+ CDbl(Me.txtprice8.Value)
End If
End Sub

However it seems to work only sometimes. At the moment when I click
cmdCalculate when the form is 'live' nothing happens at all. It has worked
before though. It seems when I get a run-time error on any different
unrelated code it stuffs this one up.

What am I missing?

Thanks in advance :)