Consulting

Results 1 to 10 of 10

Thread: Solved: Which control will gain the focus on a form

  1. #1
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location

    Solved: Which control will gain the focus on a form

    I have created a form (within Word 2000 but would apply to other office applications) & would like to be able to identify that name of the control that will receive the focus within the Exit event of the current control.

    Any ideas?

    Igwok

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    this would be the next control in the taborder so this would do it

    [VBA] Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim ctrl As Control

    For Each ctrl In UserForm1.Controls
    If ctrl.TabIndex = TextBox1.TabIndex + 1 Then MsgBox ctrl.Name
    Next

    End Sub [/VBA]
    I would imagine you'll need to refine it to accomodate controls that don't have their tabstop set so it might not be +1 you're looking for but you get the idea...
    K :-)

  3. #3
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    Thanks K but my problem lies where a user has clicked on a control elsewhere on the form. In that instance the tab order won't provide the right answer

    Igwok

  4. #4
    Knowledge Base Approver
    Space Cadet
    VBAX Tutor sandam's Avatar
    Joined
    Jan 2005
    Location
    London
    Posts
    292
    Location
    the focus order goes first in terms of tab order - sorry, its just the way both VB and VBA work. then focus is defined by whether or not the control is an input/output/command or display so eg a label won't recieve focus where as a textbox and a button will because the user interacts with it. Which is where having a well defined tab index is very necessary. the only other way to get around that would be to define the process of going through the form by what number the control is and each time they move to the "correct" next control, increase a counter. that way if they click on another control somewhere in the form, and then tab to the next control - you can in theory redirect them to the control you want them to be on.

    I hope this makes sense

    Andrew;?

    Just had an idea. You could just set the tabstop to false after a user has been through the control. that way the next tab will take them to the right control. the only downfall is that the tabstops may need to be reset so that the user can tab through the form again at a later stage.

    [vba]
    Private Sub Control1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Control1.TabStop = False
    End Sub
    [/vba]
    Last edited by Zack Barresse; 03-25-2014 at 09:54 AM. Reason: Removed bb color tags from within VBA tags.
    Nothing annoys a non-conformist more than other non-conformists who refuse to conform to the rules of non-conformity.


    Confused is my normal state of mind


  5. #5
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    No that still doesn't solve the problem - I shall set out the problem in more specific detail.

    The form I have has a number of entry fields which require completion in a particular format i.e. the right number of characters, numeric only. I written code for the exit methods on those fields where the validation occurs, cancelling the exit if the data fails.

    This works fine except that I wish to incorporate a cancel button on the form, in which case I need to incorporate into the validation code a test to check if the forms 'cancel' button has been pressed which will then allow an exit from the current field regardless of the contents.

    So the concept is the code for the exit method of a control will perform differently depending on which control has the focus upon exit.

    Igwok

  6. #6
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Ahh... now I see
    Well the exit event will always happen before the next control gets the focus so there's no way of doing it with tha validation code called from the exit event.
    Better to do it before you use the data, like on (or called from) the OK button.
    If you set which controls need validating (with the Tag property or the name if they all start with "txt") you can then just loop through them and quit the loop to return to the one that failed. Here's a basic example of what I mean:[VBA]Private Sub cmdOK_Click()

    Dim crtl As Control
    'make sure you set the Tag property of all the textboxes to be
    'validated to "1"
    For Each ctrl In UserForm1.Controls
    With ctrl
    If .Tag = 1 Then
    If .Text <> "99" Then
    'validate data
    MsgBox "Value enterd must equal 99"
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    Exit Sub
    End If
    End If
    End With
    Next ctrl
    MsgBox " All data validated"

    End Sub

    Private Sub cmdCancel_Click()
    Unload Me
    End Sub
    [/VBA]
    K :-)

  7. #7
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    I don't really want users to get through the form before validation begins so I have had a good long think about this and came up with the following solution. It is not 'ideal' as it requires code for every entry field on the form. It would be so much simpler to know which control will get the focus after the exit event has executed.

    [VBA]
    Public ProbCtrl As String 'to identify unvalidated control
    Private Sub CancelButton_Click()
    'tabstop must be set to false
    UserForm1.Hide
    End Sub
    Private Sub CtrlA_Enter()
    'all controls except the Cancel Button must have the following line
    checkvalidation
    End Sub
    Private Sub CtrlB_Enter()
    checkvalidation
    End Sub
    Private Sub CtrlA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    'tabstop must be set to true for controls requiring validation
    'if a control has already failed validation don't
    'try to validate other controls
    If ProbCtrl <> "" And ProbCtrl <> ActiveControl.Name Then Exit Sub
    'validate control - set tag to error message if fails
    If ActiveControl.Text <> "Test data" Then ActiveControl.Tag = "CtrlA is Invalid"
    'clear probctrl string.
    'If there is a problem then checkvalidation will set string
    ProbCtrl = ""
    End Sub
    Private Sub CtrlB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If ProbCtrl <> "" And ProbCtrl <> ActiveControl.Name Then Exit Sub
    If ActiveControl.Text <> "Test data" Then ActiveControl.Tag = "CtrlB is Invalid"
    ProbCtrl = ""
    End Sub
    Sub checkvalidation()
    Dim ctrl As Control
    If ProbCtrl <> "" And ProbCtrl <> ActiveControl.Name Then
    'where there is a known problem go to the next control
    SendKeys ("{TAB}")
    Else
    'check if any controls have failed validation
    'go to next control if neccessary
    For Each ctrl In UserForm1.Controls
    With ctrl
    If Len(.Tag) <> 0 Then
    'report problem
    MsgBox .Tag
    'reset tag
    .Tag = ""
    'record name of problem control
    ProbCtrl = .Name
    End If
    End With
    Next ctrl

    'go to next control if neccessary
    If ProbCtrl <> "" And ProbCtrl <> ActiveControl.Name Then SendKeys ("{TAB}")
    End If
    End Sub

    [/VBA]

    Igwok

  8. #8
    VBAX Regular
    Joined
    Sep 2013
    Posts
    18
    Location
    There would be an alternative somewhat simpler method:
    if all the fields are in a frame, and the buttons are outside the frame, then you can have an Exit frame event. Again here you cannot establish which button is getting the focus. So in the exit frame event you can set a variable to the name of the last control that was active (with PreviousControl). Then you only need enter events for the controls outside the frame and if it is the right button you ignore any checking, if it is a wrong button you still do the checking and go back to the control if required.

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Form code
    Type CtrlStatus
    CtrlName As String
    Done As Boolean
    End Type
    Create CompletedControls As Scripting Dictionary with Keys = each Ctrl's TabIndex And Each Item = CtrlStatus.CtrlNName = Each Ctrl's Name

    Ctrl's validation procedure
    Sub TextBox1_BeforeUpdate()
    '
    '
    '
    CompletedControls TextBox1.TabIndex, CtrlStatus.Done = True
    End Sub
    In Ctrl's Enter Event Sub. By including this in the Save Button's Enter Event, you can prevent Saving the data until the last input control is done.

    Sub TextBox1_Enter()
    If Not CompletedControls TextBox1.TabIndex - 1, CtrlStatus.Done Then
    SetFocusOnCorrectControl
    End If
    End Sub
    Form Code
    Sub SetFocusOnCorrectControl()
    For i = MinTabIndex to MaxTabIndex
    If Not CompletedControls i, CtrlStatus.Done Then
    Me.Controls(CompletedControls i, CtrlStatus.CtrlName).SetFocus
    Exit Sub
    End If
    Next i
    End Sub
    not tested, written in this VBAX Editor, not the VBE
    Last edited by SamT; 03-17-2014 at 08:47 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Regular
    Joined
    Sep 2013
    Posts
    18
    Location
    Sticking to VBA, I found an even better method, totally foolproof and with minimal coding:
    I noticed that the tooltips work without raising the exit event for a control. So obviously the userform kbnows where the mouse is.

    So in the situation where the action when leaving a control depends on which control the mouse is over then the following works. This could be because you don't want to raise a 'missing value' message if the user presses cancel, or as in my case, don't want to ask the user if the form should be updated with the changed values, if he presses 'Save to Sheet'.

    A private boolean flag bOn is checked by the control_Exit sub (in my case the control is a Frame where the textboxes reside)
    If the flag is true no checking is done
    The flag is set true by the sub that records if the mouse is over the 'Save to Sheet' button (or cancel button depending how you want to use it)
    The flag is set to off by the sub that records if the mouse is over the Userform.
    This is necessary as the user could just move the mouse over the button without clicking.

    Here is the simple code:
    Option Explicit
    
    
    Dim bOn  As Boolean
    
    
    Private Sub CommandButton1_Click()
        ' do save or whatever
        '....
        bOn = False
    End Sub
    
    
    Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        'set a flag that user is over the Save button, so avoid the Exit check
        bOn = True
    End Sub
    
    
    
    
    Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        ' if user clicks outside frame then check if fields in frame _
          have been filled out properly. If the bOn flag is true, then _
          the user is over a button, (such as cancel button) that means _
          the check is not required
        If bOn = False Then
            If TextBox1.Value = vbNullString Then MsgBox "Empty" Else MsgBox "Full"
        End If
    End Sub
    
    
    
    
    Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        ' set the flag back to false once user moves away from button
        bOn = False
    End Sub

Posting Permissions

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