Exit event for textbox inside class module

[6329055]

In my macro, I am creating textboxes and spinbuttons in the code, and have created a class to allow me to
deal with events. I have the following declarations inside my class:
Public WithEvents SpinButton1 As MSForms.SpinButton
Public WithEvents IntegerTextBox As MSForms.TextBox

For some reason, VBA won't let me write a
Private Sub IntegerTextBox_Exit() function inside the class, which is what I need for the error correcting code that I want to implement. Does anyone know why I can't use the Exit function inside the class?
Does anyone have a good work around?

Thanks for your help,

 

[mikerickson]

Custom controls do not have Exit events.
There are various workarounds depending on what you are doing.
Its not clear what error correction is being done. It might be possible to do it as the user types with a Change event.

OR

If the user has to click SUBMIT button after data entry, you could put a grand error checking section at the start of the SUBMIT routine.

 

[6329055]

Thanks Mike.
When I initially set up the userform, I defined the textboxes statically, and used the exit event to confirm the value entered was a number, and then applied a format to it, using the code
TextBoxName = Format(TextBoxName , "$#,##0.00").
Now that I have altered my code to create the textboxes dynamically, I have tried to put similar code in the change event, but it doesn't work as well. VBA applies the formatting as soon as one digit is entered, so the user can't enter a two digit number.
I have considered doing all error correcting and formatting when the user clicks a "check value" button, but I like the effect of the formatting applying automatically when the user clicks away from the textbox.

 

[mikerickson]

To require numeric entry, you can use the KeyPress event, which custom controls do support.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newString As String

    With TextBox1
        newString = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
    End With

    If Not (IsNumeric(newString & "0")) Then KeyAscii = 0
End Sub

 

[6329055]

Thanks. That's great - I really like that.
Any thoughts on how I could make it format the textbox automatically (insert the $ sign, display a certain number number of decimal places), once the user has entered a value?

 

[mikerickson]

Create a Label with "$" as the caption. Put it immediately to the left of the text box. If you want to be fancy, use the Change event to make the label visible if and only if there are any characters in the textbox.

(I'd give the same advice if it were a static text box. Inserting/stripping a $ from the front of a numeral every time you deal with it is a big hassle.)