F
Frank Kabel
Hi Eric
how have you defined the variable to store the sum (maybe as an
integer)?
how have you defined the variable to store the sum (maybe as an
integer)?
E
Eric van Uden
This will turn out to be a foolish question, but I have to ask...
I have a userform with 7 textboxes for users to enter values in.
The values are limited to 5 characters and are meant to represent working
hours, e.g '8', '4.25' or '10.5'.
When one of the textbox-values changes, the values in the boxes are summed
up.
This works for whole numbers, but when decimal numbers are entered, they
seem to get rounded automatically.
Thus, two entries of '2' and '2.25' would total up to '4', wheras '2' and
'2.6' would give '5'.
I need to have the real thing, though.
As textboxes are textboxes, I supposed their entered values must be
interpreted as string values, so I have experimented with formatting these
strings as numbers, using Format(strng, "#0.00"). I also tried entering the
values into separate variables (with and without dimensioning) and totalling
those - as opposed to totalling the textbox values directly - but nothing
gives.
The summing is done by setting a variable to zero, then adding one textbox
value at a time.
What did I miss (except Excel kindergarten)?
I have a userform with 7 textboxes for users to enter values in.
The values are limited to 5 characters and are meant to represent working
hours, e.g '8', '4.25' or '10.5'.
When one of the textbox-values changes, the values in the boxes are summed
up.
This works for whole numbers, but when decimal numbers are entered, they
seem to get rounded automatically.
Thus, two entries of '2' and '2.25' would total up to '4', wheras '2' and
'2.6' would give '5'.
I need to have the real thing, though.
As textboxes are textboxes, I supposed their entered values must be
interpreted as string values, so I have experimented with formatting these
strings as numbers, using Format(strng, "#0.00"). I also tried entering the
values into separate variables (with and without dimensioning) and totalling
those - as opposed to totalling the textbox values directly - but nothing
gives.
The summing is done by setting a variable to zero, then adding one textbox
value at a time.
What did I miss (except Excel kindergarten)?
M
mickedog
if your hours are enterted as text, u can convert that to a number
=VALUE("$1,000") = 1000
=VALUE("12.25") 12.25
or maybe u could use the round function
ROUND(number,num_digits)
=VALUE("$1,000") = 1000
=VALUE("12.25") 12.25
or maybe u could use the round function
ROUND(number,num_digits)
D
Dave Peterson
Long means that they can be giant numbers, but that they're all integers.
Maybe:
Dim total as Double
total = Val(hours_mon) _
+ Val(hours_tue) _
+ Val(hours_wed) _
+ Val(hours_thu) _
+ Val(hours_fri) _
+ Val(hours_sat) _
+ Val(hours_sun)
Would be sufficent.
And you'd only want that format on the caption line.
Maybe:
Dim total as Double
total = Val(hours_mon) _
+ Val(hours_tue) _
+ Val(hours_wed) _
+ Val(hours_thu) _
+ Val(hours_fri) _
+ Val(hours_sat) _
+ Val(hours_sun)
Would be sufficent.
And you'd only want that format on the caption line.
E
Eric van Uden
Hello Frank,
No, I didn't skip *that* particular class in Kindergarten
I tried 'Long'. That should represent enough memory-space to hold decimal
values, shouldn't it?
I ended up with this (overdoing it with the format function, I know):
Dim total as Long
total = Format(0, "#0.00")
If Hours_mon = "" Then total = total + 0 Else total = total +
CLng(Hours_mon)
If Hours_tue = "" Then total = total + 0 Else total = total +
CLng(Hours_tue)
If Hours_wed = "" Then total = total + 0 Else total = total +
CLng(Hours_wed)
If Hours_thu = "" Then total = total + 0 Else total = total +
CLng(Hours_thu)
If Hours_fri = "" Then total = total + 0 Else total = total +
CLng(Hours_fri)
If Hours_sat = "" Then total = total + 0 Else total = total +
CLng(Hours_sat)
If Hours_sun = "" Then total = total + 0 Else total = total +
CLng(Hours_sun)
Hours_total.Caption = Format(total , "#0.00")
Where Uren_mon through Uren_sun are the textboxes, and Uren_total is a label
in which I put the result. I have tried putting the result in a textbox or a
messagebox. No difference.
I forgot to say: if I use:
total = Application.WorksheetFunction.Sum(Hours_mon, Hours_tue, Hours_wed)
an error occurs. The value of 'Hours_mon' is shown in a tooltip when I take
my cursor into the line of code as e.g '"8"' (doublequoted, which I take to
mean text/string as opposed to number, ergo the error.
"Frank Kabel" <[email protected]> schreef in bericht
| Hi Eric
| how have you defined the variable to store the sum (maybe as an
| integer)?
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
|
| Eric van Uden wrote:
| > This will turn out to be a foolish question, but I have to ask...
| >
| > I have a userform with 7 textboxes for users to enter values in.
| > The values are limited to 5 characters and are meant to represent
| > working hours, e.g '8', '4.25' or '10.5'.
| > When one of the textbox-values changes, the values in the boxes are
| > summed up.
| > This works for whole numbers, but when decimal numbers are entered,
| > they seem to get rounded automatically.
| > Thus, two entries of '2' and '2.25' would total up to '4', wheras '2'
| > and '2.6' would give '5'.
| > I need to have the real thing, though.
| >
| > As textboxes are textboxes, I supposed their entered values must be
| > interpreted as string values, so I have experimented with formatting
| > these strings as numbers, using Format(strng, "#0.00"). I also tried
| > entering the values into separate variables (with and without
| > dimensioning) and totalling those - as opposed to totalling the
| > textbox values directly - but nothing gives.
| > The summing is done by setting a variable to zero, then adding one
| > textbox value at a time.
| >
| > What did I miss (except Excel kindergarten)?
|
No, I didn't skip *that* particular class in Kindergarten
I tried 'Long'. That should represent enough memory-space to hold decimal
values, shouldn't it?
I ended up with this (overdoing it with the format function, I know):
Dim total as Long
total = Format(0, "#0.00")
If Hours_mon = "" Then total = total + 0 Else total = total +
CLng(Hours_mon)
If Hours_tue = "" Then total = total + 0 Else total = total +
CLng(Hours_tue)
If Hours_wed = "" Then total = total + 0 Else total = total +
CLng(Hours_wed)
If Hours_thu = "" Then total = total + 0 Else total = total +
CLng(Hours_thu)
If Hours_fri = "" Then total = total + 0 Else total = total +
CLng(Hours_fri)
If Hours_sat = "" Then total = total + 0 Else total = total +
CLng(Hours_sat)
If Hours_sun = "" Then total = total + 0 Else total = total +
CLng(Hours_sun)
Hours_total.Caption = Format(total , "#0.00")
Where Uren_mon through Uren_sun are the textboxes, and Uren_total is a label
in which I put the result. I have tried putting the result in a textbox or a
messagebox. No difference.
I forgot to say: if I use:
total = Application.WorksheetFunction.Sum(Hours_mon, Hours_tue, Hours_wed)
an error occurs. The value of 'Hours_mon' is shown in a tooltip when I take
my cursor into the line of code as e.g '"8"' (doublequoted, which I take to
mean text/string as opposed to number, ergo the error.
"Frank Kabel" <[email protected]> schreef in bericht
| Hi Eric
| how have you defined the variable to store the sum (maybe as an
| integer)?
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
|
| Eric van Uden wrote:
| > This will turn out to be a foolish question, but I have to ask...
| >
| > I have a userform with 7 textboxes for users to enter values in.
| > The values are limited to 5 characters and are meant to represent
| > working hours, e.g '8', '4.25' or '10.5'.
| > When one of the textbox-values changes, the values in the boxes are
| > summed up.
| > This works for whole numbers, but when decimal numbers are entered,
| > they seem to get rounded automatically.
| > Thus, two entries of '2' and '2.25' would total up to '4', wheras '2'
| > and '2.6' would give '5'.
| > I need to have the real thing, though.
| >
| > As textboxes are textboxes, I supposed their entered values must be
| > interpreted as string values, so I have experimented with formatting
| > these strings as numbers, using Format(strng, "#0.00"). I also tried
| > entering the values into separate variables (with and without
| > dimensioning) and totalling those - as opposed to totalling the
| > textbox values directly - but nothing gives.
| > The summing is done by setting a variable to zero, then adding one
| > textbox value at a time.
| >
| > What did I miss (except Excel kindergarten)?
|
E
Eric van Uden
Hello Mickedog,
Your solution would do fine in the worksheet, of course, but the VALUE
function doesn't seem to exist in VBA and
total = Application.WorksheetFunction.Round(Hours_mon, 2)
ends in an error. Round doesn't work on textstrings, or so it seems.
Or did I misread your answer?
Eric
"mickedog >" <<[email protected]> schreef in bericht
| if your hours are enterted as text, u can convert that to a number
| =VALUE("$1,000") = 1000
| =VALUE("12.25") 12.25
|
| or maybe u could use the round function
|
| ROUND(number,num_digits)
|
|
| ---
| Message posted
|
Your solution would do fine in the worksheet, of course, but the VALUE
function doesn't seem to exist in VBA and
total = Application.WorksheetFunction.Round(Hours_mon, 2)
ends in an error. Round doesn't work on textstrings, or so it seems.
Or did I misread your answer?
Eric
"mickedog >" <<[email protected]> schreef in bericht
| if your hours are enterted as text, u can convert that to a number
| =VALUE("$1,000") = 1000
| =VALUE("12.25") 12.25
|
| or maybe u could use the round function
|
| ROUND(number,num_digits)
|
|
| ---
| Message posted
|
M
mickedog
reply to Eric van Uden
i have not had the patients to dive in to vba yet, so I just play wit
the spread sheet functions.
I used to play with qbasic, but its been awhile.
as a work around to your problem, could you store your info as text
then change it to a value on the spread sheet?
"1.25 hours" stored in whatever variable
i have not had the patients to dive in to vba yet, so I just play wit
the spread sheet functions.
I used to play with qbasic, but its been awhile.
as a work around to your problem, could you store your info as text
then change it to a value on the spread sheet?
"1.25 hours" stored in whatever variable
E
Eric van Uden
Hello Dave,
You helped me out.
Mickedog got me working on Application.Worksheetfunction.Round(), and that
presented me with the tooltip:
Round(Arg1 as double, arg2 as double) as double.
So I figured Double might be the dimension.
You gave me the confirmation.
Totaling the way you suggested didn't work however, but I now have:
Dim total as Double
total = 0
If Hours_mon = "" Then total = total + 0 Else total = total +
CDbl(Hours_mon)
If Hours_tue = "" Then total = total + 0 Else total = total +
CDbl(Hours_tue)
If Hours_wed = "" Then total = total + 0 Else total = total +
CDbl(Hours_wed)
If Hours_thu = "" Then total = total + 0 Else total = total +
CDbl(Hours_thu)
If Hours_fri = "" Then total = total + 0 Else total = total +
CDbl(Hours_fri)
If Hours_sat = "" Then total = total + 0 Else total = total +
CDbl(Hours_sat)
If Hours_sun = "" Then total = total + 0 Else total = total +
CDbl(Hours_sun)
Hours_total.Caption = Format(total , "#0.00")
and I'm a happy camper again.
Thank you for your help, Dave, mickedog and Frank.
My dear is that GMT+1!? Where's my pillow?
--
Eric
"Dave Peterson" <[email protected]> schreef in bericht
| Long means that they can be giant numbers, but that they're all integers.
|
| Maybe:
|
| Dim total as Double
| total = Val(hours_mon) _
| + Val(hours_tue) _
| + Val(hours_wed) _
| + Val(hours_thu) _
| + Val(hours_fri) _
| + Val(hours_sat) _
| + Val(hours_sun)
|
| Would be sufficent.
|
| And you'd only want that format on the caption line.
|
| Eric van Uden wrote:
| >
| > Hello Frank,
| >
| > No, I didn't skip *that* particular class in Kindergarten
| >
| > I tried 'Long'. That should represent enough memory-space to hold
decimal
| > values, shouldn't it?
| > I ended up with this (overdoing it with the format function, I know):
| >
| > Dim total as Long
| > total = Format(0, "#0.00")
| > If Hours_mon = "" Then total = total + 0 Else total = total +
| > CLng(Hours_mon)
| > If Hours_tue = "" Then total = total + 0 Else total = total +
| > CLng(Hours_tue)
| > If Hours_wed = "" Then total = total + 0 Else total = total +
| > CLng(Hours_wed)
| > If Hours_thu = "" Then total = total + 0 Else total = total +
| > CLng(Hours_thu)
| > If Hours_fri = "" Then total = total + 0 Else total = total +
| > CLng(Hours_fri)
| > If Hours_sat = "" Then total = total + 0 Else total = total +
| > CLng(Hours_sat)
| > If Hours_sun = "" Then total = total + 0 Else total = total +
| > CLng(Hours_sun)
| > Hours_total.Caption = Format(total , "#0.00")
| >
| > Where Uren_mon through Uren_sun are the textboxes, and Uren_total is a
label
| > in which I put the result. I have tried putting the result in a textbox
or a
| > messagebox. No difference.
| >
| > I forgot to say: if I use:
| >
| > total = Application.WorksheetFunction.Sum(Hours_mon, Hours_tue,
Hours_wed)
| >
| > an error occurs. The value of 'Hours_mon' is shown in a tooltip when I
take
| > my cursor into the line of code as e.g '"8"' (doublequoted, which I take
to
| > mean text/string as opposed to number, ergo the error.
| >
| > "Frank Kabel" <[email protected]> schreef in bericht
| > | > | Hi Eric
| > | how have you defined the variable to store the sum (maybe as an
| > | integer)?
| > |
| > | --
| > | Regards
| > | Frank Kabel
| > | Frankfurt, Germany
| > |
| > |
| > | Eric van Uden wrote:
| > | > This will turn out to be a foolish question, but I have to ask...
| > | >
| > | > I have a userform with 7 textboxes for users to enter values in.
| > | > The values are limited to 5 characters and are meant to represent
| > | > working hours, e.g '8', '4.25' or '10.5'.
| > | > When one of the textbox-values changes, the values in the boxes are
| > | > summed up.
| > | > This works for whole numbers, but when decimal numbers are entered,
| > | > they seem to get rounded automatically.
| > | > Thus, two entries of '2' and '2.25' would total up to '4', wheras
'2'
| > | > and '2.6' would give '5'.
| > | > I need to have the real thing, though.
| > | >
| > | > As textboxes are textboxes, I supposed their entered values must be
| > | > interpreted as string values, so I have experimented with formatting
| > | > these strings as numbers, using Format(strng, "#0.00"). I also tried
| > | > entering the values into separate variables (with and without
| > | > dimensioning) and totalling those - as opposed to totalling the
| > | > textbox values directly - but nothing gives.
| > | > The summing is done by setting a variable to zero, then adding one
| > | > textbox value at a time.
| > | >
| > | > What did I miss (except Excel kindergarten)?
| > |
|
| --
|
| Dave Peterson
| (e-mail address removed)
You helped me out.
Mickedog got me working on Application.Worksheetfunction.Round(), and that
presented me with the tooltip:
Round(Arg1 as double, arg2 as double) as double.
So I figured Double might be the dimension.
You gave me the confirmation.
Totaling the way you suggested didn't work however, but I now have:
Dim total as Double
total = 0
If Hours_mon = "" Then total = total + 0 Else total = total +
CDbl(Hours_mon)
If Hours_tue = "" Then total = total + 0 Else total = total +
CDbl(Hours_tue)
If Hours_wed = "" Then total = total + 0 Else total = total +
CDbl(Hours_wed)
If Hours_thu = "" Then total = total + 0 Else total = total +
CDbl(Hours_thu)
If Hours_fri = "" Then total = total + 0 Else total = total +
CDbl(Hours_fri)
If Hours_sat = "" Then total = total + 0 Else total = total +
CDbl(Hours_sat)
If Hours_sun = "" Then total = total + 0 Else total = total +
CDbl(Hours_sun)
Hours_total.Caption = Format(total , "#0.00")
and I'm a happy camper again.
Thank you for your help, Dave, mickedog and Frank.
My dear is that GMT+1!? Where's my pillow?
--
Eric
"Dave Peterson" <[email protected]> schreef in bericht
| Long means that they can be giant numbers, but that they're all integers.
|
| Maybe:
|
| Dim total as Double
| total = Val(hours_mon) _
| + Val(hours_tue) _
| + Val(hours_wed) _
| + Val(hours_thu) _
| + Val(hours_fri) _
| + Val(hours_sat) _
| + Val(hours_sun)
|
| Would be sufficent.
|
| And you'd only want that format on the caption line.
|
| Eric van Uden wrote:
| >
| > Hello Frank,
| >
| > No, I didn't skip *that* particular class in Kindergarten
| >
| > I tried 'Long'. That should represent enough memory-space to hold
decimal
| > values, shouldn't it?
| > I ended up with this (overdoing it with the format function, I know):
| >
| > Dim total as Long
| > total = Format(0, "#0.00")
| > If Hours_mon = "" Then total = total + 0 Else total = total +
| > CLng(Hours_mon)
| > If Hours_tue = "" Then total = total + 0 Else total = total +
| > CLng(Hours_tue)
| > If Hours_wed = "" Then total = total + 0 Else total = total +
| > CLng(Hours_wed)
| > If Hours_thu = "" Then total = total + 0 Else total = total +
| > CLng(Hours_thu)
| > If Hours_fri = "" Then total = total + 0 Else total = total +
| > CLng(Hours_fri)
| > If Hours_sat = "" Then total = total + 0 Else total = total +
| > CLng(Hours_sat)
| > If Hours_sun = "" Then total = total + 0 Else total = total +
| > CLng(Hours_sun)
| > Hours_total.Caption = Format(total , "#0.00")
| >
| > Where Uren_mon through Uren_sun are the textboxes, and Uren_total is a
label
| > in which I put the result. I have tried putting the result in a textbox
or a
| > messagebox. No difference.
| >
| > I forgot to say: if I use:
| >
| > total = Application.WorksheetFunction.Sum(Hours_mon, Hours_tue,
Hours_wed)
| >
| > an error occurs. The value of 'Hours_mon' is shown in a tooltip when I
take
| > my cursor into the line of code as e.g '"8"' (doublequoted, which I take
to
| > mean text/string as opposed to number, ergo the error.
| >
| > "Frank Kabel" <[email protected]> schreef in bericht
| > | > | Hi Eric
| > | how have you defined the variable to store the sum (maybe as an
| > | integer)?
| > |
| > | --
| > | Regards
| > | Frank Kabel
| > | Frankfurt, Germany
| > |
| > |
| > | Eric van Uden wrote:
| > | > This will turn out to be a foolish question, but I have to ask...
| > | >
| > | > I have a userform with 7 textboxes for users to enter values in.
| > | > The values are limited to 5 characters and are meant to represent
| > | > working hours, e.g '8', '4.25' or '10.5'.
| > | > When one of the textbox-values changes, the values in the boxes are
| > | > summed up.
| > | > This works for whole numbers, but when decimal numbers are entered,
| > | > they seem to get rounded automatically.
| > | > Thus, two entries of '2' and '2.25' would total up to '4', wheras
'2'
| > | > and '2.6' would give '5'.
| > | > I need to have the real thing, though.
| > | >
| > | > As textboxes are textboxes, I supposed their entered values must be
| > | > interpreted as string values, so I have experimented with formatting
| > | > these strings as numbers, using Format(strng, "#0.00"). I also tried
| > | > entering the values into separate variables (with and without
| > | > dimensioning) and totalling those - as opposed to totalling the
| > | > textbox values directly - but nothing gives.
| > | > The summing is done by setting a variable to zero, then adding one
| > | > textbox value at a time.
| > | >
| > | > What did I miss (except Excel kindergarten)?
| > |
|
| --
|
| Dave Peterson
| (e-mail address removed)