Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    userform textbox numeric format (97)

    I am designing a userform. It has several TextBoxes that will read values (i.e. 4.589). I want the values to be displayed in a predefined numeric format and to keep that format whenever the values are changed.

    How do I set the userform textbox numeric format?

    Thanks
    Guillermo

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    Textboxes store text strings, not numbers. Therefore, there is no number format. What you would have to do is put code in the textbox change event routine to format the number like you want it and put the it back as a string. Be sure to set Application.EnableEvents=False before storing the string in the textbox and set it back to true after. If you don't you will end up in an endless loop.
    Legare Coleman

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: userform textbox numeric format (97)

    I use the exit event to format the entry as I want, so the required format is shown when the User leaves the particular textbox
    e.g. for Textbox20:

    Private Sub Textbox20_Exit(ByVal Cancel etc etc)
    Textbox20 = Format(Textbox20,"#,##0.00")
    End Sub

    You could also check for blanks etc etc.

    zeddy

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    Should I be using another control for storing numbers?

    Or format it the right way?

    Application.EnableEvents=False gave me a clue about other problems I was having with my code

    THANK YOU
    Guillermo

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    I don't know of any standard controls that will do what you are looking for. There are probably some available that you could buy to do this, but I don't know anything specific.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    Great help!!

    how would you check for blanks, and input a "0" if blank?

    is there any control like TextBox, but for numbers (NumBox?) so I dont have to go through all the extra code?

    THANK YOU!!
    Guillermo

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    If you use the following code on conjuction with zeddy's, any character other than a number, a minus sign (-) or a decimal seperator (.) will NOT be accepted by the text box.<pre>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 45, 46, 48 To 57
    KeyAscii = KeyAscii
    Case Else
    KeyAscii = 0
    End Select
    End Sub</pre>

    where TextBox1 is the name of your textbox.

    Andrew C

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: userform textbox numeric format (97)

    Andrew,

    What is the purpose of the line
    <pre> KeyAscii = KeyAscii
    </pre>

    StuartR

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: userform textbox numeric format (97)

    There's no real purpose: it just makes it obvious which key strokes are kept and which are rejected.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    Andrew: I just noticed something in your code that brings up a question. If KewAscii is being passed ByVal, then setting it equal to something in this routine should not do anything since it would only change the value on the parameter stack, not the variable it is being passed from. I looked it up in the Help file, and that is what it says there also. I think it must be being passed ByRef, no matter what you specify and no matter what the Help file says.
    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    Legare,

    See if this snip from Help clarifies :
    <hr>The ByVal keyword in Microsoft Forms indicates that an argument is passed as a value; this is the standard meaning of ByVal in Visual Basic. However, in Microsoft Forms, you can use ByVal with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString object. When you do, the value passed is not a simple data type; it is a pointer to the object.

    When used with these objects, ByVal refers to the object, not the method of passing parameters. Each of the objects listed above has a Value property that you can set. You can also pass that value into and out of a function. Because you can change the values of the object's members, events produce results consistent with ByRef behavior, even though the event syntax says the parameter is ByVal.

    Assigning a value to an argument associated with a ReturnBoolean, ReturnEffect, ReturnInteger, or ReturnString is no different from setting the value of any other argument. For example, if the event syntax indicates a Cancel argument used with the ReturnBoolean object, the statement Cancel=True is still valid, just as it is with other data types.<hr>
    ByVal KeyAscii As MSForms.ReturnInteger seems to have the same effect as ByRef.

    I actually gave the matter no thought as I just took the Event coding skeleton from the drop down in the UserForm codepane.

    Andrew

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: userform textbox numeric format (97)

    OK, but I thought that passing a pointer to the parameter was the definition of ByRef, Don't you love Microsoft's consistency!
    Legare Coleman

Posting Permissions

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