+ Reply to Thread
Results 1 to 18 of 18

Wrong calculation results in userform textbox when using more than 3 digits for a number

  1. 05-05-2013, 04:58 PM #1
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Wrong calculation results in userform textbox when using more than 3 digits for a number

    Greetings,
    I am using this code for calculating few textbox's in a userform 

    Please Login or Register  to view this content.
    the sum of all values are appearing in textbox4 , but if I entered a number more than 3 digits ( 1000 ) the value in textbox4 will appear 1

    but if I remove
    Please Login or Register  to view this content.
    Textbox4 show correct results

    How to keep all values using the format "#,###,##0.00" without any problem ???
    I am sure something is missing with the code I'm using !!
    Help Needed
    Reply With QuoteRegister To Reply
  2. 05-05-2013, 11:53 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Hello Shacker,

    I am not really sure what you want to achieve. I can tell you the Format statement converts a number into a formatted string. It will not format a string value into a formatted string.

    All data in a TextBox is text. It neither recognizes or converts numeric characters into numbers. The code below has been modified to take the TextBox value convert it into a number and the format it as string with decimal separators and 2 digits after the zero.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 05-06-2013, 09:45 AM #3
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Thank you Leith....

    I tried the code you amend , but didn't work , still giving wrong sum amount
    I am attaching an example , please check

    thanks again for your reply

    Invoicing - new test.xlsm
    Reply With QuoteRegister To Reply
  4. 05-06-2013, 09:55 AM #4
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    It's because you've formatted to include commas.

    This will add correctly.
    Please Login or Register  to view this content.
    By the way, why are you using the variable n?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  5. 05-06-2013, 10:17 AM #5
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    you are correct , but still I need the Amount to show in commas ( 150,000.00 )
    and so on ...
    Reply With QuoteRegister To Reply
  6. 05-06-2013, 10:21 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Why?

    It seems as though that will just cause problems, isn't it more important that the calculation is correct?

    If you really want the fomatting then do it after the calculation.
    Reply With QuoteRegister To Reply
  7. 05-06-2013, 11:00 AM #7
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    WHY ???
    Coz the currency value should look like this ....

    Thank you guys for your support ...

    sometimes its better to say : Sorry we don't know how to do it .. instead of asking WHY ??? there is no limit in programming and I'm sure there will always be an answer ...

    Leith Ross , Thank you . you were GREAT
    Reply With QuoteRegister To Reply
  8. 05-06-2013, 11:05 AM #8
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    I posted how to do it, format after the calculation not before.
    Reply With QuoteRegister To Reply
  9. 05-06-2013, 01:44 PM #9
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Hello Shaker,

    Thanks for posting the workbook. I apologize for not responding earlier. Here is the working code and workbook.

    The problem of using commas to separate the values has been solved by using the CCur function in VBA. This function is regionally aware. If you system is setup for US currency then you will have no problems. If some other currency is selected then the TextBox output will be in the selected currency's format. If you have any problems, let me know so I can fix it.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 05-06-2013, 01:56 PM #10
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    WOW ... thanks a millllion
    easy for me now to continue
    you are amazing Leith

    Appreciated :D
    Reply With QuoteRegister To Reply
  11. 05-06-2013, 02:00 PM #11
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Hello Shacker,

    You'er welcome.
    Reply With QuoteRegister To Reply
  12. 05-06-2013, 02:18 PM #12
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Leith ,
    I am Sorry , but on the first row its ok , when choosing the second row it gives me an error
    "Run-time error "13"
    type mismatch

    any idea why ?
    Reply With QuoteRegister To Reply
  13. 05-06-2013, 02:22 PM #13
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Try this.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 05-06-2013, 02:27 PM #14
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Hello Shacker,

    The copy of your workbook I modified works will all the TextBoxes, no matter where I start first. Tell me exactly what you did so I can test it on my copy.
    Reply With QuoteRegister To Reply
  15. 05-06-2013, 02:32 PM #15
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Perfect ,....

    you are a HERO
    its working smoothly now

    Reply With QuoteRegister To Reply
  16. 05-06-2013, 02:43 PM #16
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Last Question here ...

    how to add this code 
    Please Login or Register  to view this content.
    to this formula ??
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  17. 05-06-2013, 02:53 PM #17
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    The code would probably look something like this, which takes care of any commas.
    Please Login or Register  to view this content.
    Where it should go depends on when you want the calculation to occur.

    Would it be after TextBox33 has exited?
    Reply With QuoteRegister To Reply
  18. 05-06-2013, 03:33 PM #18
    Shacker
    Shacker is offline
    Registered User
    Join Date
    04-23-2013
    Location
    Jordan
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Wrong calculation results in userform textbox when using more than 3 digits for a numb

    Thank you Norie
    Thank you Leith
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1