none
Exit Event for Textbox included in a Frame RRS feed

  • Question

  • Excel 2010 VBA

    I have three textboxes in a frame; I use the frame so that these three textboxes only become visible after other textboxes have successfully received user input. When I tab between the textboxes within the frame, the textbox Exit event fires normally when passing from the first to the second, and the second to the third. However, when I tab out of the third textbox --  and also leave the frame -- the Exit event for the third textbox doesn't fire until I return focus somewhere in the frame. The problem seems to be when tabbing out of the textbox at the same time leaving the frame. It seem like I exit the frame and move onto another textbox not in the frame before I exit the final textbox that is in the frame.

    Thanks in advance for any help.

    Wednesday, July 13, 2011 9:45 PM

All replies

  • The same will happen when you are on textbox 1 or 2 and you click on a control outside of your frame I expect.

    One way to overcome this is use the Enter events of the controls inside the fram to store the "current" control. Then use the Frame's Exit event to detect what was the active control in the frame when you left the frame.

    Mind you: the textbox's exit event WILL fire when you close the form!

    Example: Three textboxes inside a frame and this code behind the form:

    Option Explicit

    'Variable to hold last textbox active in frame
    Dim moCtl As Control

    Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        MsgBox "frame exit from:" & moCtl.Name
    End Sub

    Private Sub TextBox1_Enter()
        Set moCtl = TextBox1
    End Sub

    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        MsgBox "tb1"
    End Sub

    Private Sub TextBox2_Enter()
        Set moCtl = TextBox2
    End Sub

    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        MsgBox "tb2"
    End Sub

    Private Sub TextBox3_Enter()
        Set moCtl = TextBox3
    End Sub

    Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        MsgBox "tb3"
    End Sub


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Thursday, July 14, 2011 7:21 AM
  • It appears you must set focus back to a control within the Frame from the control that obtains focus outside of the frame.  This is a big programming problem if there are a lot of controls on the user form.  This is not a clean solution to the problem.

    Although you can validate most textbox data on an event exit, this situation is a difficult programming problem to fix.  I have found that the best I can do is to revalidate all fields upon a command button click and then to reset focus on invalid textboxes if they fail a validity test.  Another work around is to call a general subroutine that validates all textboxes on a User Form when entering each textbox, but this has other problems of its own .  This is clumsy but it works. 

    ' create a UserForm
    ' insert a frame
    ' insert one textbox in frame
    ' insert second textbox outside of frame
    ' insert third textbox inside frame
    ' insert command button
    ' plug in this code and test it out for yourself.
    
    Private Sub CommandButton1_Click() ' will check all textbox edits and return to invaid entry.
      If Not Edit_Fields Then
        Exit Sub
      End If
      MsgBox "continue"
    End Sub
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' checks validity when exiting to another control within frame.
      If edit_textbox1 = False Then
        MsgBox "cancel Textbox1"
        Cancel = True
      End If
    End Sub
    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' checks validity ouside frame.
      If edit_textbox2 = False Then
        MsgBox "cancel Textbox2"
        Cancel = True
      End If
    End Sub
    Private Function edit_textbox2() As Boolean  ' edit textbox 2
       If TextBox2.Value = "" Then
         edit_textbox2 = True
       Else
         edit_textbox2 = False
         MsgBox "return to field 2"
         TextBox2.SetFocus
         Exit Function
       End If
    End Function
    Private Function edit_textbox1() As Boolean  ' edit textbox 1
       If TextBox1.Value = "" Then
         edit_textbox1 = True
       Else
         edit_textbox1 = False
         MsgBox "return to field 1"
         TextBox1.SetFocus
         Exit Function
       End If
    End Function
    Private Function Edit_Fields() As Boolean  ' checks all edits
       If edit_textbox1 = False Then
         Edit_Fields = False
         Exit Function
       End If
       If TextBox2.Value = "" Then
         Edit_Fields = True
       Else
         Edit_Fields = False
         MsgBox "return to field 2"
         TextBox2.SetFocus
         Exit Function
       End If
    End Function





    • Edited by Wm D Gilman Tuesday, February 25, 2014 7:40 PM
    Tuesday, February 25, 2014 6:15 AM