Exit event for textbox inside class module

6329055

New Member
Joined
Oct 27, 2013
Messages
3
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,
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,320
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

New Member
Joined
Oct 27, 2013
Messages
3
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,320
To require numeric entry, you can use the KeyPress event, which custom controls do support.

Code:
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

New Member
Joined
Oct 27, 2013
Messages
3
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,320
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.)
 

Forum statistics

Threads
1,181,852
Messages
5,932,448
Members
436,839
Latest member
Mama247

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top