Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Textbox decimal places

    Hi all,

    I have set the control source for a textbox in a form to a cell in which the formatting is a number with 2 digits after the decimal. When the contents of the cell is, say .70, the textbox displays .7, when the contents of the cell is say .65, the textbox displays .65. Is there any way to force the textbox to display two digits after the decimal? I did not see anything in the properties panel which might indicate the possibility of setting this.

    Thanks,

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox decimal places

    You do this through Format/Cells/Number/Custom (or right click on the cell and select Format Cells). Once you're there you should see a number of options. The ones you want look like #,##0.00 or maybe like #,##0.00;(#,##0.00) - there are a number of built in options, or you can define your own.

    Brooke

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Textbox decimal places

    Hi Mike,
    I don't know of any easy way of doing that unless your textbox is always bound to the same cell in which case you would use code to update the value in the cell from the userform rather than directly binding the textbox to the cell. If you use the controlsource property, then what you get is, as you've found, the stored value rather than the displayed one.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox decimal places

    Hi all,

    The custom format did not change the results in the textbox. I did, however, update the textbox contents in the form_activate event and used the textbox_change event to update the cell as Rory suggested.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox decimal places

    If the text box is in a form, you can put code into the BeforeUpdate event for the text box, as follows:

    TextBox1.Value = Format(txtTest1.Value, "0.00")

    When you type a number into the box and leave the box (either by Enter, Tab, Arrow, click on another control, etc.), the number will be reformatted to two decimal places. If you type something other than a number, nothing will change. If you wanted to get fancier, you could put more code, checking to see if the new entry IsNumeric or not, as follows:

    With TextBox1
    If IsNumeric(.Value) Then
    .Value = Format(.Value, "0.00")

    Else
    .SelStart = 0
    .SelLength = Len(.Text)
    Cancel = True
    MsgBox "Please enter a number"

    End If

    End With


    The Cancel = True causes the focus to remain on the text box. The SelStart and SelLength cause the text they typed to be selected, so the user can immediately type a new entry without having to manually erase the previous entry. The MsgBox, of course, tells them the reason the entry is not valid.

    If the text box is an ActiveX control on a worksheet, I'm not sure how to do this, but I suspect there is a similar method. If it is a simple text box from the "Forms" toolbar, I don't think it is possible, but I could be wrong.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox decimal places

    Thanks Jim, that looks very interesting, I did not know how to torture a text box so much! Much obliged!

Posting Permissions

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