yes, there are - can't say why but I've never employed them. Application.OnTime is one way to fire an action, there's also a Timer property to get the return system time. I'm short on experience here. Tushar Mehta has some sample uses at his site I believe (tushar-mehta.com). There's really loads of examples here at MrExcel if you want to try some searches (perhaps someone else can chime in here).
It's pretty straightforward to set up a class module that should work with all the textboxes on the userform.
For example you could try this.
1 Create a class module (Insert>Class Module) and call it CTextboxes.
2 Enter this code in the class module.
Public WithEvents TextGroup As MSForms.TextBox
Private Sub TextGroup_Change()
If Len(TextGroup.Value) > 0 And Not IsNumeric(TextGroup.Value) Then
MsgBox "Only numeric data, thank you"
TextGroup.Value = Left(TextGroup.Value, Len(TextGroup.Value) - 1)
3 In the userform module enter this code.
Dim TextBoxes() As New CTextboxes
Private Sub CommandButton1_Click()
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
I = 1
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ReDim Preserve TextBoxes(1 To I)
Set TextBoxes(I).TextGroup = ctl
I = I + 1
This code shoulf prevent anything other than numeric values being entered in the textboxes and give the user a messag telling telling them not to.
It's quite old code - I think I wrote it more than 2 years ago and it might not be the best approach, I was still trying to learn about classes in VBA at the time.
But it seemed to work for me then and still seems to work now.
PS I've got to ask this - why do you have so many textboxes on the userform?
hi thanks for the code, i see what you are trying to do there and it is so much more efficient then mine
to answer your question, my form is a tool for technicians to do monthly checks on our power equipments. and these equipments have so many paramenters that needs to be verified, like frequency, 3 phase voltage, current etc. and if any of them goes wrong(eg out of range) the forms needs to alert the technician to contact me for the issue.