Page 1 of 4 123 ... LastLast
Results 1 to 10 of 39

VBA Multiple Textbox Exit Event Question

This is a discussion on VBA Multiple Textbox Exit Event Question within the Excel Questions forums, part of the Question Forums category; Hi guys. Been a while since I needed your help, but I'm back again. The project: Form with lots of ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    48

    Default VBA Multiple Textbox Exit Event Question

    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

  2. #2
    Board Regular
    Join Date
    Apr 2011
    Posts
    219

    Default Re: VBA Multiple Textbox Exit Event Question

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

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    1,973

    Default Re: VBA Multiple Textbox Exit Event Question

    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.

  4. #4
    New Member
    Join Date
    Mar 2010
    Posts
    48

    Default Re: VBA Multiple Textbox Exit Event Question

    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.

  5. #5
    Board Regular
    Join Date
    Jun 2011
    Location
    Hurricane Lane
    Posts
    97

    Default Re: VBA Multiple Textbox Exit Event Question

    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.

  6. #6
    Board Regular
    Join Date
    Jun 2002
    Posts
    1,973

    Default Re: VBA Multiple Textbox Exit Event Question

    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:
    Code:
    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.
    Code:
    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.
    Code:
    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.
    Last edited by ChrisM; Jul 7th, 2011 at 11:34 PM.

  7. #7
    New Member
    Join Date
    Mar 2010
    Posts
    48

    Default Re: VBA Multiple Textbox Exit Event Question

    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

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Posts
    1,973

    Default Re: VBA Multiple Textbox Exit Event Question

    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.

  9. #9
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,712

    Default Re: VBA Multiple Textbox Exit Event Question

    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:
    
    ' 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
    Vladimir Zakharov
    Microsoft MVP Excel

  10. #10
    New Member
    Join Date
    Mar 2010
    Posts
    48

    Default Re: VBA Multiple Textbox Exit Event Question

    @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

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com