Textbox adding problem

[Todd Huttenstine]

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

 

[Rob van Gelder]

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

 

[tolgag]

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.

 

[Todd Huttenstine]

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]

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]

YES! That works perfect. Will you please explain that logic to me? Im
still learning all this stuff. Thank you.

 

[Rob van Gelder]

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

 

[Paul Robinson]

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

 

[Tom Ogilvy]

or even

If InStr(1, OrderForm.TextBox19.Value, "Select", vbTextCompare) = 0 Then _
curTemp = curTemp + CCur(OrderForm.TextBox19.Value)

 

[Tom Ogilvy]

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

 

[Paul Robinson]

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

 

[Guest]

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