Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
just a question:
have you tried playing with the format option before passing the value? TextBox1.Value = Format(TextBox1.Value, "00.00") / 3 Duncan pmguerra wrote: 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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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 pmguerra wrote: 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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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 pmguerra wrote: 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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
Do you have some formatting occur in one of the TextBox events ?
Does this work ? TextBox1.Text= CSng(TextBox1.Text) / 3 NickHK "pmguerra" wrote in message ... 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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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 ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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!!! NickHK Wrote: Do you have some formatting occur in one of the TextBox events ? Does this work ? TextBox1.Text= CSng(TextBox1.Text) / 3 NickHK -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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... Duncan Wrote: 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 pmguerra wrote: 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??? -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox calculation problem...
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!!! ;) -- pmguerra ------------------------------------------------------------------------ pmguerra's Profile: http://www.excelforum.com/member.php...o&userid=14986 View this thread: http://www.excelforum.com/showthread...hreadid=561409 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox calculation | Excel Programming | |||
TextBox calculation | Excel Programming | |||
Textbox calculation | Excel Programming | |||
Date Calculation in UserForm TextBox | Excel Programming | |||
Userforms - Display result of a calculation in a textbox | Excel Programming |