Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > code to sum 8 textboxes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 113
Default 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 :)
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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)) _
+ 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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154415

Microsoft Office Help

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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 :)
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 113
Default 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 :)
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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 :)
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 747
Default 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 :)

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
code for conditional validation to textboxes tkraju via OfficeKB.com Excel Programming 6 June 5th 08 06:11 PM
Show Results in TextBoxes, ONLY if All TextBoxes have Data in them RyanH Excel Programming 3 November 19th 07 03:30 PM
textboxes libby Excel Programming 5 April 13th 04 06:32 PM
Addition code for 110 TextBoxes John Wilson Excel Programming 4 January 27th 04 03:41 AM
Addition code for 110 TextBoxes Todd Huttenstine[_3_] Excel Programming 1 January 27th 04 03:11 AM


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement