Textbox adding problem

T

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
 
R

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
 
T

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.
 
T

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
 
R

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
 
T

Todd Huttenstine

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

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
 
P

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
 
T

Tom Ogilvy

or even

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

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 $
Click to expand...

Textboxes only store strings
Also, your code will be more efficient if you use
With Userform
.TextBox.Value
end with
Click to expand...

Perhaps, but for the example given, it isn't
 
P

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
 
R

Rob

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
 
Top