Textbox calculation problem...

[pmguerra]

Hi again...

I have a little textbox problem. The code (simplified) is the
following:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'divide the textbox value by 3
TextBox1.Value = TextBox1.Value / 3

End Sub

when I insert "55", I get 18.333. No problem here.

If I insert "55.55", I get 1851.6667!!!!!!!!!!!!!!! I believe this is a
dot or comma related problem...

Any help, please???

 

[Duncan]

just a question:

have you tried playing with the format option before passing the value?

TextBox1.Value = Format(TextBox1.Value, "00.00") / 3

Duncan

 

[Duncan]

Try the format option first, but I dont think that is the problem
having tested your code, there must be something else that is setting
the format of the textbox....

I am not sure but is there something in the textbox properties that is
dictating the format?, or is it how the value is passed?

Try my line of code replacing yours but post back and let us know
because I am not sure if that will solve it for you (I cannot get 55.55
/ 3 to equal 1851.6667 on my test so it must be something else that
your form is doing, the format might solve it but that is not the cause
only the symptom)

Post back and let us know

Duncan

 

[Duncan]

Found it.

it is because of using commas. if you want it to work with commas where
the dots are supposed to be then put this code first which will replace
the commas for dots for the purposes of calculating.

s1 = textbox1.Text
s1 = Replace(s1, ",", ".")

But that depends on how big a number you want in the textbox and
whether you need to comma delimit thousands or millions.

For instance

6,000,000.01 should be typed in as 6000000.01


Duncan

 

[NickHK]

Do you have some formatting occur in one of the TextBox events ?
Does this work ?

TextBox1.Text= CSng(TextBox1.Text) / 3

NickHK

 

[pmguerra]

I've uploaded pictures of my form's behaviour. (second textbox is only
for exit event of textbox1)

I've tried this code at home and now in another PC. The result is the
same...

With your code, absolutely no change...

My decimal separator is "dot" and I'm NOT using regional settings (dot
for thousands and comma for decimals).


+-------------------------------------------------------------------+
|Filename: Nova imagem (1).JPG |
|Download: http://www.excelforum.com/attachment.php?postid=5031 |
+-------------------------------------------------------------------+

 

[pmguerra]

Yes I've tried the Type conversion function... It also doesn't doesn't
work

Another funny thing is that I found this code because I thought it
could work:

' Declare variables for data entry validation alert dialog
Dim Msg, Style, Title, Response
' If any data exists in text box...
If Len(TextBox1.Text) > 0 Then
' Validate each keystroke entered in the textbox...
If Not IsNumeric(Right(TextBox1.Text, 1)) Then
' Alert user data must be a number
' Define user dialog parameters
Msg = "The data just entered must be a number." & Chr(13) &
_
" Do you want try again?"
Style = vbRetryCancel + vbExclamation + vbDefaultButton1
Title = "Data Type Error"
' Display alert dialog
Response = MsgBox(Msg, Style, Title)
' If user chooses Cancel, clear the textbox & stop
validation until next keystroke
If Response = vbCancel Then
TextBox1.Text = ""
Exit Sub
End If
'User chose to retry, so strip last character entered from
textbox
TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) -
1)
End If
End If

But once I type "." or "," it brings out an message box. This prevents
me from inserting decimal values!!!

 

[pmguerra]

It still doesn't work...

So that there is no doubt, I want "dot" do be the decimal separator and
I don't want any thousands separator. Even if I did, it would a "space"
character...

This is getting on my nerves...

 

[pmguerra]

Well, I've figured it out...

I had to change the system's regional settings... From commas to
dots...

I thought VBA used excel's definitions...

Anyway, thanks for all your help!!!