userform textbox numeric format (97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!!

Reply | Quote

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.

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

where TextBox1 is the name of your textbox.

Andrew C

Reply | Quote

Andrew,

What is the purpose of the line

            KeyAscii = KeyAscii

StuartR

Reply | Quote

There’s no real purpose: it just makes it obvious which key strokes are kept and which are rejected.

Reply | Quote

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.

Reply | Quote

Legare,

See if this snip from Help clarifies :
[indent]

---

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.

---

[/indent]
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

Reply | Quote

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

Reply | Quote