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

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

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

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]

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

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]

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

[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

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.

Form code

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

Code:

---

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.

Code:

---

Sub TextBox1_Enter()
If Not CompletedControls TextBox1.TabIndex - 1, CtrlStatus.Done Then
SetFocusOnCorrectControl
End If
End Sub

---

Form Code

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

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:

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

---