VBA Multiple Textbox Exit Event Question

[Annie Bender]

Hi guys. Been a while since I needed your help, but I'm back again. The project: Form with lots of textbox controls (dynamic, up to ~150). I want to do validation upon exiting these controls, but don't want to have a separate Textbox_Exit sub for each one of them to call the identical validation test (contained in one validation sub). How can a single "generic" Textbox_Exit sub be written to apply to each of the 150+/- controls that require validation? Is this where a class comes in? If so, that's new programming to me, so some guidance would really be appreciated. Actually, however it has to be done, I need your help. Thanks as always for straightening me out.

--Annie in Florida

 

[Sthrncali]

Could you tie your validation script to the "submit" button or whatever you are using for the user to "save" changes...

I.E.

For Each Control in Userform1.Controls
If Control.Type Like "TextBox" Then
'Perform Validation
End If
Next

That syntax may not be exact but I have done similiar data validation at the end of a userform by cycling through the controls by either Control.Name or Control.Type then performing data validation that way.

If invalid data is entered you can change properties of the text box to indicate to the user, such as making background color red, or font red, etc..

 

[ChrisM]

You're on the right track. In full Visual Basic, this would be called a control array. You write code one time and the system tells you which particular control in the array was clicked or activated.

VBA doesn't support control arrays. To get around it, you can use a class module. I did the same thing to make a calendar control. Had 40 labels that I didn't want to code individually.

Unfortunately, I'm at work and that code is at home. I can post it later tonight if you don't get any other responses.

 

[Annie Bender]

Hey Chris. I can imagine how a control array might work, but not yet up-to-speed on a class module, so I'm looking forward to anything further you might be able to post for me.

@Sthrncali, I sure could do it easily at the Commandbutton_Click event, but I just think it could get more tedious for the user if all the validation errors (assuming there are any) are saved up and the user is hit with a wave of these things. Seems less "ugly" from the user's POV if a single error is caught right away. Anyway, that would be a fall-back option for me as a novice programmer if I can't learn a more elegant approach.

 

[BrandonWLH]

Sth has a good idea with that if your not class comfortable. For the errors, throw them into a dynamic array. Use the array index to match the textBox #, loop through the controls and then color code the text boxes based upon the error and have a color code key.

So you hit submit, 5 boxes turn to red text for invalid, 8 turn to blue text for improper formatting or blank, etc.

 

[ChrisM]

Ok, found my code, there are a couple steps involved. I will post my code, which again is used for a calendar control, with 40 labels representing the days of this month +/- a week or so. The idea is that the user clicking any of the labels will fire off just one procedure.

1) First, create a class module from the insert menu. Give the module a relevant name, it will be used in your code. Mine is called clsControlEvents
2) Add this code to the class module:

Public WithEvents Lbl As MSForms.Label
Public Frm As UserForm

Private Sub Lbl_Click()
    Lbl.BorderColor = &HFF& 'this is where you set the main action you want to occur
End Sub

3) Now let's move chronologically, starting with when the user clicks one of my date textboxes.

Private Sub txtExpCloseDate_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        'i've stripped out some other stuff, here's the only relevant bits. Clicking the textbox
        'launches my calendar form
        frmCal.Calendar_Change
        frmCal.Show
        'now my calendar form is showing, and the user is about to click on one of the labels representing a day of the month.
End Sub

4) Here's where the magic happens. You have to modify this. Since I'm loading a calendar form, all my code is tied to the form initialize event. You will want to change this to something else, probably the initialize event of your main form. Here we loop through all the controls on the form. I set the tag property of the labels I wanted to use with numbers 1 thru 42. Those labels that are tagged get the special Class treatment. Labels that are just plain old labels get skipped this way.

Dim colLabels As New Collection 'this is a form level variable

Private Sub UserForm_Initialize()
'initialize class variables
Dim Ctl As MSForms.Control
Dim obEvents As clsControlEvents

For Each Ctl In Me.Controls
    If TypeOf Ctl Is MSForms.Label And Ctl.Tag <> "" Then
        Set obEvents = New clsControlEvents
        Set obEvents.Lbl = Ctl   'Lbl and Frm were the two variables dimmed in the class module remember?
        Set obEvents.Frm = Me
        colLabels.Add obEvents
    End If
Next Ctl

End Sub

5) That should do it. At this point, if any of the labels get clicked, the first procedure back in the class module will get kicked off.

Hopefully you followed all that, and you can modify this to suit your own needs.

 

[Annie Bender]

Thanks Chris. Reading through it, it seems generally understandable. The real test will come when I actually try to adapt the coding to my specific situation. At first glance, I'm not sure how the snippet at paragraph 3) would apply, since the validation action is not an initialization step. Maybe that coding wouldn't apply? But it is clear that the TextBox_Exit Sub with validation steps goes into the class module, and the form itself sets the objects (at least the tagged ones) to the class events. Right?

I'll get to the coding a bit later today, and may have follow-up Qs then. Thanks everyone for your help so far.

--Annie

 

[ChrisM]

Yes, I agree with all that.

Step 3) is certainly not needed in your case, I included it so you could get the flow of how my implementation works.

Remember tagging the labels was just my convention, you don't have to do that. Use whatever means necessary to add the correct textboxes to the class. You could prefix them all the same, then test the first couple letters of the control name for example.

 

[ZVI]

For regret class module does not support Exit/Enter events for MSForms.TextBox.
The workaround can be in using of one common validation subroutine for the numerous texboxes like this:

' Code in userform module
' It's assumed that there are 3 texboxes on the form
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  MyValidation Cancel
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  MyValidation Cancel
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  MyValidation Cancel
End Sub

' Common validation subroutine
Private Sub MyValidation(Optional ByVal Cancel As MSForms.ReturnBoolean)
  Dim tb As MSForms.TextBox
  Set tb = Me.ActiveControl
  ' Put your validation code below
  If Not IsNumeric(tb) Then
    MsgBox "Should be numeric value", vbExclamation, tb.Name
    Cancel = True
  End If
End Sub

 

[Annie Bender]

@ZVI: Are you saying that the Exit event doesn't work in a class module at all? In other words, there is no way I can adapt Chris' example as long as I am trying to use an Exit event for a TextBox?

The workaround you suggest, however, is not a workaround for what I want. In fact, it's just what I DON'T want to have to do. After all, there may be as many as 150 textboxes that would have to have individual Subs written for them to call the validation routine.

Do others agree with ZVI that the Exit event isn't supported? I guess I will find out soon enough, when I have time to play with some coding. TIA

--Annie in Florida