+ Reply to Thread
Results 1 to 7 of 7

Formatting TextBox While Not Messing Up Calculations?

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    28

    Formatting TextBox While Not Messing Up Calculations?

    Hi,

    I'm building a sort of ratings model for work usbing VBA/Userforms.

    I've pretty much finished what I wanted to do calculations wise, except now I want to tidy the userform up a bit.

    One of my main pet peeves is when you input numbers into textboxes they are displayed with no format whatsoever.

    I know how to format a textbox after making a calculation. (i.e. after you click the command button the textboxes then get formatted)

    But how do you format a textbox from the get go, so that as you type into the textbox, the textbox automatically displays the data in your chosen format.

    In this example I want my textbox to be displayed as so "$ #,###,###"

    I tried entering the following code in the textbox private sub:


    TextBox14.Value = Format(TextBox14.Value, "$#,###,##0")

    And that did add the dollar sign to the textbox but not the commas, however, what is worse is that now none of my calucations work as the userform doesn't seem to be able to read the value in the textbox anymore.

    Any help would be much appreciated.

    Thanks,

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Formatting TextBox While Not Messing Up Calculations?

    Where exactly did you put that code?

    If you want calculations to work you'll need to convert the value in the textbox to a number.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Formatting TextBox While Not Messing Up Calculations?

    I put the code here (which is why the calculations don't work anymore)

    Private Sub TextBox14_Change()
    TextBox14.Value = Format(TextBox14.Value, "$#,###,##0")
    End Sub

    If I put the code here (after all my calculation code) then everything works fine, but the textbox only gets formatted after the calculations are made not instantly as you type them in.

    Private Sub CommandButton1_Click()
    Textbox14.Value = Textbox12.Value * Textbox13.Value
    TextBox14.Value = Format(TextBox14.Value, "$#,###,##0")
    End Sub

    (Obviously the code for all my calculations isn't that simple, but this is just for illustrative purposes)

    I'm getting the impression that you can't format a textbox prior to calculation, and that there is no way for for vba to only read the numbers when calculating and ignore the additional format characters.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Formatting TextBox While Not Messing Up Calculations?

    The value in a textbox is text, to use it in most calculations you need to convert it to a number, even if it has no additional fomatting characters.

    However there are exceptions, for example when multiplying, as in your example, the conversion happens automatically, provided there are no additional characters.

    It's pretty straightforward to do the conversion, though having the $ and , does complicate things.

    For example.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Formatting TextBox While Not Messing Up Calculations?

    I probably did something wrong but that didn't work for me. I've attached an example userform, and basically all I want done is to make it so that when you enter number values in textbox 1 and 2 they automatically/instantly display with dollar sign aand comma (Where applicable) and not mess up the calculation.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Formatting TextBox While Not Messing Up Calculations?

    Try this.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Formatting TextBox While Not Messing Up Calculations?

    Thank You Norie, thanks for your patience and helping me.

    I get it now, and can apply the same method you provided to my original userform.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro's messing up calculations of named ranges
    By imatomic in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2014, 11:37 AM
  2. [SOLVED] Conditional Formatting Messing Up When Delete Cells
    By jamiek47 in forum Excel General
    Replies: 3
    Last Post: 11-12-2013, 08:33 AM
  3. Conditional Formatting Messing up in Macros
    By rayuken in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2013, 10:10 AM
  4. [SOLVED] VBA Textbox Calculations
    By zhb12810 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2012, 12:10 PM
  5. userform calculations in textbox
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2007, 07:36 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1