Results 1 to 9 of 9

Number format in userform textbox

This is a discussion on Number format in userform textbox within the Excel Questions forums, part of the Question Forums category; Hi everyone, Can anyone tell me how to control the format of numbers (i.e. amount of decimal places) in a ...

  1. #1
    Board Regular Atholl's Avatar
    Join Date
    May 2002
    Location
    Scotland
    Posts
    434

    Default

    Hi everyone,
    Can anyone tell me how to control the format of numbers (i.e. amount of decimal places) in a textbox within a userform. The userform seems to ignore the formatting on the worksheet. I assume you can change this within the properties section of the textbox, but I can't seem to find what controls number format.
    Cheers,

    Atholl

  2. #2
    Vas
    Vas is offline
    Board Regular
    Join Date
    May 2002
    Location
    Gothenburg, Sweden
    Posts
    74

    Default

    It's not that simple. If the user writes in numbers in a different format, how are you going to solve that?

    One good way is to use the Format function. See helpfile for info on that.
    /Niklas Jansson

  3. #3
    Board Regular Atholl's Avatar
    Join Date
    May 2002
    Location
    Scotland
    Posts
    434

    Default

    The thing is, the textboxes I want to format display the outputs of my spreadsheet, so I know approximately the size of the numbers

    Best Regards,

    Atholl

  4. #4
    Vas
    Vas is offline
    Board Regular
    Join Date
    May 2002
    Location
    Gothenburg, Sweden
    Posts
    74

    Default

    Then use the Format function:

    txtValue = Format(Value, "000.00")

    Change the "000.00" to whatever you like. Use *'s and 0's

    /Niklas Jansson

  5. #5
    Board Regular Atholl's Avatar
    Join Date
    May 2002
    Location
    Scotland
    Posts
    434

    Default

    Thanks, Vas!


    [ This Message was edited by: Atholl on 2002-06-04 03:15 ]

  6. #6
    New Member
    Join Date
    Feb 2009
    Location
    Australia
    Posts
    47

    Default Re: Number format in userform textbox

    Does anyone know how to capture a date and time entry in a userform textbox and then when I insert it into a cell value, the value will display as the cell is formatted instead of how the userform formats it?

    I have a row of cells formatted like this: d/m "@" hhmm"h"
    I can enter a date time combination like this: 28/5 16:00
    And Excel makes the call value like this: 28/05/2011 4:00:00 PM
    If in the UserForm I use: Format(Me.TextBox1.Value, "d/m \@ hhmm\h")
    Excel will make the cell value like this: 28/5 @ 1600h

    How can I make the cell value equal the full date time group, yet display the formatted date time group from a userform?

    Thanks in advance.

  7. #7
    New Member
    Join Date
    Feb 2009
    Location
    Australia
    Posts
    47

    Default Re: Number format in userform textbox

    I have tried numerous options. The only way to do it appears to put it in the format that it likes. Then enter the cell and press enter.

    However, this code keep looping. Why?

    Code:
    Private Sub CommandButton4_Click()
    num = Range("A50").Value
    num = num + num + 5
    Cells(4, num).Value = Me.ComboBox4.Value
    Cells(6, num).Value = Me.TextBox4.Value
    Cells(8, num).Value = Format(Me.TextBox5.Value, "d/mm/yyyy") & " " & Format(Left(Me.TextBox6.Value, 2) & ":" & Right(Me.TextBox6.Value, 2), "h:mm:ss AM/PM")
    Cells(8, num).Select
    SendKeys "{F2}", True
    SendKeys "~", True
    End Sub

  8. #8
    New Member
    Join Date
    Feb 2009
    Location
    Australia
    Posts
    47

    Default Re: Number format in userform textbox

    Sorted...hitting "Enter" from within a UserForm was simply re-igniting the commandbutton...

    Is there any way to keep this userform open and sendkeys to the sheet...like:

    Sheets("NOV").SendKeys "{F2}" ???

  9. #9
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    3,004

    Default Re: Number format in userform textbox

    Tobbes,

    Using the textbox exit event works for me, as per example below. You obviously need to make sure to code around there being any unexpected exits that may overwrite your desired date capture.


    Code:
    Private Sub tbxDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Sheets("Management").Range("A30").Value = Me.tbxDate.Value
    End Sub
    Tony

    AbUsing Excel 2002, 2007
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com