Userform, global textbox change event?

robertrobert905

Board Regular
Joined
Jun 27, 2008
Messages
139
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?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Jun 27, 2008
Messages
139
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

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

Board Regular
Joined
Jun 27, 2008
Messages
139
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

ztus85.jpg
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
Extra points for a very nice presentation - 100 textboxes or not...

ξ
 

Watch MrExcel Video

Forum statistics

Threads
1,122,656
Messages
5,597,390
Members
414,141
Latest member
Joey_T92

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