| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox adding problem
Hey Rob, this is your code
I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Looks like the + operator in this case is acting as a string concatenator
instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Hi,
1. If you enter the data like "$40" in the text box, it's no more a numeric value. You should enter the values just as numbers, like "40". If you want to show the unts, make a label next to the text box. 2. You defined a string variable, so in this case you concetanate them. Try to define a long variable. --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Here is what I did and now no number is poping up in TextBox119 at all.
Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur(OrderForm.TextBox9.Value) + CCur(OrderForm.TextBox14.Value) + _ CCur(OrderForm.TextBox19.Value) + CCur(OrderForm.TextBox24.Value) + _ CCur(OrderForm.TextBox29.Value) + CCur(OrderForm.TextBox34.Value) + _ CCur(OrderForm.TextBox39.Value) + CCur(OrderForm.TextBox44.Value) + _ CCur(OrderForm.TextBox49.Value) + CCur(OrderForm.TextBox54.Value) + _ CCur(OrderForm.TextBox59.Value) + CCur(OrderForm.TextBox64.Value) + _ CCur(OrderForm.TextBox69.Value) + CCur(OrderForm.TextBox74.Value) + _ CCur(OrderForm.TextBox79.Value) + CCur(OrderForm.TextBox84.Value) + _ CCur(OrderForm.TextBox89.Value) + CCur(OrderForm.TextBox94.Value) + _ CCur(OrderForm.TextBox99.Value) + CCur(OrderForm.TextBox104.Value) + _ CCur(OrderForm.TextBox109.Value) + CCur(OrderForm.TextBox114.Value) OrderForm.TextBox119.Value = Replace(curTemp, "Select", "") End Sub "Rob van Gelder" wrote in message ... Looks like the + operator in this case is acting as a string concatenator instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Todd,
Something like this maybe? Private Sub CommandButton1_Click() Dim curTemp As Currency curTemp = 0 If Not CBool(InStr(1, OrderForm.TextBox19.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox19.Value) If Not CBool(InStr(1, OrderForm.TextBox24.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox24.Value) ... OrderForm.TextBox119.Value = Format(curTemp, "Currency") End Sub Rob "Todd Huttenstine" wrote in message ... Here is what I did and now no number is poping up in TextBox119 at all. Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur(OrderForm.TextBox9.Value) + CCur(OrderForm.TextBox14.Value) + _ CCur(OrderForm.TextBox19.Value) + CCur(OrderForm.TextBox24.Value) + _ CCur(OrderForm.TextBox29.Value) + CCur(OrderForm.TextBox34.Value) + _ CCur(OrderForm.TextBox39.Value) + CCur(OrderForm.TextBox44.Value) + _ CCur(OrderForm.TextBox49.Value) + CCur(OrderForm.TextBox54.Value) + _ CCur(OrderForm.TextBox59.Value) + CCur(OrderForm.TextBox64.Value) + _ CCur(OrderForm.TextBox69.Value) + CCur(OrderForm.TextBox74.Value) + _ CCur(OrderForm.TextBox79.Value) + CCur(OrderForm.TextBox84.Value) + _ CCur(OrderForm.TextBox89.Value) + CCur(OrderForm.TextBox94.Value) + _ CCur(OrderForm.TextBox99.Value) + CCur(OrderForm.TextBox104.Value) + _ CCur(OrderForm.TextBox109.Value) + CCur(OrderForm.TextBox114.Value) OrderForm.TextBox119.Value = Replace(curTemp, "Select", "") End Sub "Rob van Gelder" wrote in message ... Looks like the + operator in this case is acting as a string concatenator instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
YES! That works perfect. Will you please explain that logic to me? Im
still learning all this stuff. Thank you. "Rob van Gelder" wrote in message ... Todd, Something like this maybe? Private Sub CommandButton1_Click() Dim curTemp As Currency curTemp = 0 If Not CBool(InStr(1, OrderForm.TextBox19.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox19.Value) If Not CBool(InStr(1, OrderForm.TextBox24.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox24.Value) ... OrderForm.TextBox119.Value = Format(curTemp, "Currency") End Sub Rob "Todd Huttenstine" wrote in message ... Here is what I did and now no number is poping up in TextBox119 at all. Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur(OrderForm.TextBox9.Value) + CCur(OrderForm.TextBox14.Value) + _ CCur(OrderForm.TextBox19.Value) + CCur(OrderForm.TextBox24.Value) + _ CCur(OrderForm.TextBox29.Value) + CCur(OrderForm.TextBox34.Value) + _ CCur(OrderForm.TextBox39.Value) + CCur(OrderForm.TextBox44.Value) + _ CCur(OrderForm.TextBox49.Value) + CCur(OrderForm.TextBox54.Value) + _ CCur(OrderForm.TextBox59.Value) + CCur(OrderForm.TextBox64.Value) + _ CCur(OrderForm.TextBox69.Value) + CCur(OrderForm.TextBox74.Value) + _ CCur(OrderForm.TextBox79.Value) + CCur(OrderForm.TextBox84.Value) + _ CCur(OrderForm.TextBox89.Value) + CCur(OrderForm.TextBox94.Value) + _ CCur(OrderForm.TextBox99.Value) + CCur(OrderForm.TextBox104.Value) + _ CCur(OrderForm.TextBox109.Value) + CCur(OrderForm.TextBox114.Value) OrderForm.TextBox119.Value = Replace(curTemp, "Select", "") End Sub "Rob van Gelder" wrote in message ... Looks like the + operator in this case is acting as a string concatenator instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Todd,
Instr returns 0 if it doesn't find a match, 0 if it does find a match. False = 0, True < 0 - So that's where the CBool comes to play. If TextBox19.Value does not contain the word "Select" then curTemp = curTemp + TextBox19.Value converted to a Currency datatype. Rob "Todd Huttenstine" wrote in message ... YES! That works perfect. Will you please explain that logic to me? Im still learning all this stuff. Thank you. "Rob van Gelder" wrote in message ... Todd, Something like this maybe? Private Sub CommandButton1_Click() Dim curTemp As Currency curTemp = 0 If Not CBool(InStr(1, OrderForm.TextBox19.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox19.Value) If Not CBool(InStr(1, OrderForm.TextBox24.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox24.Value) ... OrderForm.TextBox119.Value = Format(curTemp, "Currency") End Sub Rob "Todd Huttenstine" wrote in message ... Here is what I did and now no number is poping up in TextBox119 at all. Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur(OrderForm.TextBox9.Value) + CCur(OrderForm.TextBox14.Value) + _ CCur(OrderForm.TextBox19.Value) + CCur(OrderForm.TextBox24.Value) + _ CCur(OrderForm.TextBox29.Value) + CCur(OrderForm.TextBox34.Value) + _ CCur(OrderForm.TextBox39.Value) + CCur(OrderForm.TextBox44.Value) + _ CCur(OrderForm.TextBox49.Value) + CCur(OrderForm.TextBox54.Value) + _ CCur(OrderForm.TextBox59.Value) + CCur(OrderForm.TextBox64.Value) + _ CCur(OrderForm.TextBox69.Value) + CCur(OrderForm.TextBox74.Value) + _ CCur(OrderForm.TextBox79.Value) + CCur(OrderForm.TextBox84.Value) + _ CCur(OrderForm.TextBox89.Value) + CCur(OrderForm.TextBox94.Value) + _ CCur(OrderForm.TextBox99.Value) + CCur(OrderForm.TextBox104.Value) + _ CCur(OrderForm.TextBox109.Value) + CCur(OrderForm.TextBox114.Value) OrderForm.TextBox119.Value = Replace(curTemp, "Select", "") End Sub "Rob van Gelder" wrote in message ... Looks like the + operator in this case is acting as a string concatenator instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Hi
I'd guess the output from your text boxes are strings. In that case, the + operator assumes you want concatenation of strings and so acts like &. Hence the $30 + $40 appears as the string $30$40. If your input to the textboxes is numerical, but formatted as $, try Val(Textbox.Value). Also, your code will be more efficient if you use With Userform .TextBox.Value end with and don't have multiple occurrences of Userform. regards Paul "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
or even
If InStr(1, OrderForm.TextBox19.Value, "Select", vbTextCompare) = 0 Then _ curTemp = curTemp + CCur(OrderForm.TextBox19.Value) -- Regards, Tom Ogilvy "Rob van Gelder" wrote in message ... Todd, Instr returns 0 if it doesn't find a match, 0 if it does find a match. False = 0, True < 0 - So that's where the CBool comes to play. If TextBox19.Value does not contain the word "Select" then curTemp = curTemp + TextBox19.Value converted to a Currency datatype. Rob "Todd Huttenstine" wrote in message ... YES! That works perfect. Will you please explain that logic to me? Im still learning all this stuff. Thank you. "Rob van Gelder" wrote in message ... Todd, Something like this maybe? Private Sub CommandButton1_Click() Dim curTemp As Currency curTemp = 0 If Not CBool(InStr(1, OrderForm.TextBox19.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox19.Value) If Not CBool(InStr(1, OrderForm.TextBox24.Value, "Select")) Then curTemp = curTemp + CCur(OrderForm.TextBox24.Value) ... OrderForm.TextBox119.Value = Format(curTemp, "Currency") End Sub Rob "Todd Huttenstine" wrote in message ... Here is what I did and now no number is poping up in TextBox119 at all. Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur(OrderForm.TextBox9.Value) + CCur(OrderForm.TextBox14.Value) + _ CCur(OrderForm.TextBox19.Value) + CCur(OrderForm.TextBox24.Value) + _ CCur(OrderForm.TextBox29.Value) + CCur(OrderForm.TextBox34.Value) + _ CCur(OrderForm.TextBox39.Value) + CCur(OrderForm.TextBox44.Value) + _ CCur(OrderForm.TextBox49.Value) + CCur(OrderForm.TextBox54.Value) + _ CCur(OrderForm.TextBox59.Value) + CCur(OrderForm.TextBox64.Value) + _ CCur(OrderForm.TextBox69.Value) + CCur(OrderForm.TextBox74.Value) + _ CCur(OrderForm.TextBox79.Value) + CCur(OrderForm.TextBox84.Value) + _ CCur(OrderForm.TextBox89.Value) + CCur(OrderForm.TextBox94.Value) + _ CCur(OrderForm.TextBox99.Value) + CCur(OrderForm.TextBox104.Value) + _ CCur(OrderForm.TextBox109.Value) + CCur(OrderForm.TextBox114.Value) OrderForm.TextBox119.Value = Replace(curTemp, "Select", "") End Sub "Rob van Gelder" wrote in message ... Looks like the + operator in this case is acting as a string concatenator instead of an addition. Because the $ is in front of the text, it's treating them all as strings. You could wrap each with a CCur() function. eg. CCur(OrderForm.TextBox9.Value) + CCur( ... Sub CalculateProfit() Dim curTemp As Currency curTemp = CCur("$30") + CCur("$40") + CCur("$50") End Sub Because of the way VB does datatype conversions, only the first CCur() is needed, but to be 100% sure, specify them all. It's generally considered bad programming practise to rely on auto datatype conversions. It holds true for Date datatypes especially. Rob "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
From the immediate window:
? val("$40") 0 Val stops evaluating when it hits the dollar sign. Maybe you meant ? cdbl("$40") 40 If the textbox contains a $ then the value of the Textbox includes the $. Not sure what you are driving at with If your input to the textboxes is numerical, but formatted as $ Textboxes only store strings Also, your code will be more efficient if you use With Userform .TextBox.Value end with Perhaps, but for the example given, it isn't -- Regards, Tom Ogilvy Paul Robinson wrote in message om... Hi I'd guess the output from your text boxes are strings. In that case, the + operator assumes you want concatenation of strings and so acts like &. Hence the $30 + $40 appears as the string $30$40. If your input to the textboxes is numerical, but formatted as $, try Val(Textbox.Value). Also, your code will be more efficient if you use With Userform .TextBox.Value end with and don't have multiple occurrences of Userform. regards Paul "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
Fair enough!
I was working on the assumption that the value in each textbox was an integer, and that the integer was formatted as dollar currency (as opposed to someone typing in $40 for example). When I tested this, I put 40 in a cell and formatted as dollars (so I see $40) then Val applied to that gave me 40. Forgot, of course, that the textbox value is a string regardless... I can only access this NewsGroup via Google too, so even though I'm well down the reply list, I was replying first as far as I could see. Hence I look like a bit of a dope who hasn't read the thread - as well as being dopey of course. regards Paul "Tom Ogilvy" wrote in message ... From the immediate window: ? val("$40") 0 Val stops evaluating when it hits the dollar sign. Maybe you meant ? cdbl("$40") 40 If the textbox contains a $ then the value of the Textbox includes the $. Not sure what you are driving at with If your input to the textboxes is numerical, but formatted as $ Textboxes only store strings Also, your code will be more efficient if you use With Userform .TextBox.Value end with Perhaps, but for the example given, it isn't -- Regards, Tom Ogilvy Paul Robinson wrote in message om... Hi I'd guess the output from your text boxes are strings. In that case, the + operator assumes you want concatenation of strings and so acts like &. Hence the $30 + $40 appears as the string $30$40. If your input to the textboxes is numerical, but formatted as $, try Val(Textbox.Value). Also, your code will be more efficient if you use With Userform .TextBox.Value end with and don't have multiple occurrences of Userform. regards Paul "Todd Huttenstine" wrote in message ... Hey Rob, this is your code I have 22 Product Types. You select the product from the ComboBox. When you select the product, all the textboxes auto populate. Each product has 3 textboxes. One is Number of Units, next is Unit Cost, next is Unit Sale. Every product has these textboxes and these 3 text boxes have direct impact over profit. In each of these 3 textbox change events I put the code CalculateProfit so it will call that procedure from the module. The module contains this code below. My problem is when I select multiple products, textbox 119 does not add the textboxes up, it just keeps adding the price into the textbox. For example, one product I select has a profit of $30, the next has a profit of $40, instead of textbox 119 displaying $80, it displays $30$40. This pattern continues. Why is this happening? Is it happening because the code is in the change event for each product so the code calculates it up individual? I would like for it to add it so $80 displays. Sub CalculateProfit() Dim strTemp as String strTemp = OrderForm.TextBox9.Value + OrderForm.TextBox14.Value + OrderForm.TextBox19.Value + OrderForm.TextBox24.Value + _ OrderForm.TextBox29.Value + OrderForm.TextBox34.Value + OrderForm.TextBox39.Value + OrderForm.TextBox44.Value + _ OrderForm.TextBox49.Value + OrderForm.TextBox54.Value + OrderForm.TextBox59.Value + OrderForm.TextBox64.Value + _ OrderForm.TextBox69.Value + OrderForm.TextBox74.Value + OrderForm.TextBox79.Value + OrderForm.TextBox84.Value + _ OrderForm.TextBox89.Value + OrderForm.TextBox94.Value + OrderForm.TextBox99.Value + OrderForm.TextBox104.Value + _ OrderForm.TextBox109.Value + OrderForm.TextBox114.Value OrderForm.TextBox119.Value = Replace(strTemp, "Select", "") End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox adding problem
You mentioned Date datatypes. I'm having a similar problem with getting VBA to recognise my Dates. I take a string from a form combobox, and put it into a date variable, which works, but recognises the date as if it was in mm/dd/yy format rather than the format it's actually in: dd/mm/y
Private Sub CountDate_DropButtonClick( Dim DteCountDate As Dat DteCountDate = CountDate 'this assumes that countdate is in mm/dd/yy forma CountDate.Clea For i = -5 To 5: CountDate.AddItem Format$(DteCountDate + i, "dd/mm/yy"): Nex CountDate = DteCountDat End Su Private Sub CountDate_Exit(ByVal Cancel As MSForms.ReturnBoolean CountDate.Text = Format$(CountDate.Value, "dd/mm/yy" End Su |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with TAB key and Textbox controls | Excel Programming | |||
| Dynamically Adding Textbox To Frame | Excel Worksheet Functions | |||
| Problem with TextBox & ControlSource - Please Help | Excel Programming | |||
| TextBox SetFocus Problem | Excel Programming | |||
| Excel textbox problem | Excel Programming | |||
![Todd Huttenstine[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
