Userform, global textbox change event?

[robertrobert905]

What if you have over 100 textboxes and you want the function WARNINGS() to be called if any of the textboxes had a change event?

Do I have to paste Call Warnings() in every textboxes' change event?

 

[xenou]

Short answer, yes - but you may be able to use find and replace to do a quicker job (find End Sub and replace with Call Warnings() End Sub or something like that)

Long answer, this may be a way to get this done, essentially searching for "textboxes" whenever a control is clicked:
http://www.mrexcel.com/forum/showthread.php?t=355176

Or, search Google for Textbox Class Event site:mrexcel.com
There are several posts on this topic but textboxes look to be unexpectedly tricky even for advanced Excel programmers.

 

[robertrobert905]

thanks, i guess i will just use your first suggestion then.

Also does vba have timers? maybe i can call my warning function every 0.1 second

 

[xenou]

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).

ξ

 

[Norie]

ADVERTISEMENT

What does the function actually do?

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.

Option Explicit
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)
    End If
End Sub

3 In the userform module enter this code.

Dim TextBoxes() As New CTextboxes
 
Private Sub CommandButton1_Click()
    Unload Me
End Sub
 
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
        End If
    Next ctl

End Sub

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?

 

[robertrobert905]

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.

preview of a tab

 

[xenou]

Extra points for a very nice presentation - 100 textboxes or not...

ξ