Userform controls - SetFocus

[dean12]

The simple idea of moving from control to control is driving me nuts. I have 3 fields:

Start Time
Stop Time
Duration

Here are the rules. We come to the start time field:

1. When we leave if the field is blank then skip Stop and prompt for duration.

2. When we leave if the field is not blank then go to the stop time field for input.

Placing some type of setfocus statement in the AfterUpdate of Start time works only if the user updates the field. If the field is empty (default) then tab leaves the field unchanged and we go to stop which is wrong (default tab order). AfterUpdate does not even execute.

Placing a setfocus in the Exit event of start time doesn't seem to work - and there also seems to be an issue of the start Exit event executing twice. The setFocus in the exit event triggers the exit event for the same control a 2nd time.

Oh boy, it sure seems messy.

 

[dean12]

More interesting stuff. The tab order is:

Start
Stop
Duration

All fields are enabled. In the AfterUpdate event for START is say if the control is empty then disable STOP. If the control value is not blank then enable STOP.

I go to the START field. I enter a value and TAB. We move to the STOP field. Using the mouse I go back to START, clear the field and TAB. Nothing happens! I have to hit the tab a second time and then we jump over the Stop field and go to Duration.

It is like Excel has figured out where it wants to go next while we are in the START field. Then when we get ready to leave (AfterUpdate) the destination is suddenly disabled and Excel says "Oh s....." and doesn't move.

 

[SkipVought]

hi,

It is all apparently magic, since you post no rational for this behavior, and there is no question asked, what kind of response do you expect?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I started Excel 2003, VBA editor, placed 3 text boxes on a UserForm (I assume that's the 3 'fields' you are talking about):

Option Explicit

Private Sub txtStartTime_Exit(ByVal Cancel As _
MSForms.ReturnBoolean)

If Trim(txtStartTime.Text) = "" Then
    txtStopTime.Text = ""
    txtStopTime.Enabled = False
    txtDuration.SetFocus
Else
    txtStopTime.Enabled = True
    txtStopTime.SetFocus
End If

End Sub

Works just fine.

The only problem I see is - sometimes when I type a number into txtStartTime and hit the Tab to move to another textbox, the text in txtStartTime box gets highlighted and focus does not move to the next control. Hit Tab again and you move OK.

Have fun.

---- Andy

 

[TonyJollans]

Using SetFocus in another control's event is extremely difficult - and doesn't really work effectively.

When you are in the exit event of a control, you are still in that control. When you set the focus to another control it immediately triggers an exit from the control you are in (the one whose exit event code is already running), and it fires up a second instance of the exit event code (as you have seen) and then it enters the control you specified, and then (after it has run its entry routine if it has one), code returns to where the original setfocus came from - this forces focus back to the first control, which then forces an exit from the control you want to be in.

I hope you could follow that! Judicious use of Cancel (subject to knowing how you got there) in Exit routines can help suppress the default behaviour - you will need some trial and error depending on what other controls you have, and what other code you have running in any of their event code.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[dean12]

Sorry I forgot the question. It is: Why is this not doing what I expect?

I built an Excel 2010 workbook with a single command button to open a user form. The user form has 4 combo boxes labeled comboF1 thru comboF4. A listbox tracks firing of events but it is optional. Here's all the userform code:

Option Explicit

Private Sub comboF1_AfterUpdate()

lbStack.AddItem "comboF1_AfterUpdate"

If comboF1.ListIndex = -1 Then
comboF2.Enabled = False
comboF2.ListIndex = -1
combof3.Enabled = True
combof3.SetFocus
Else
comboF2.Enabled = True
combof3.Enabled = False
comboF2.SetFocus
End If

End Sub

Private Sub combof3_AfterUpdate()

lbStack.AddItem "comboF3_AfterUpdate"

If combof3.ListIndex = -1 Then
comboF1.Enabled = True
Else
comboF1.Enabled = False
End If

End Sub

Private Sub UserForm_Activate()

lbStack.AddItem "UserForm_Activate"

comboF1.AddItem "a"
comboF2.AddItem "b"
combof3.AddItem "c"
combof4.AddItem "d"

End Sub



I ran two test trials - one with the setfocus statements disabled and one with them enabled. Neither trial does what I would expect. The idea is this:

If comboF1 is empty (listindex=-1) then disable comboF2 and go to comboF3. If comboF1 has a value (listindex <> -1) then enable comboF2 and go to comboF2.

The problem behavior seems to come up when comboF1 and comboF2 are in direct tab order with each other - there is no intermediate control. Here's what I saw with the two trials:

Trial #1 – No SETFOCUS
Open form. Activate event fires. Position at f1.
Tab. Jump to f2.
Tab. Jump to f3.
Tab. Jump to f4.
Tab. Jump to f1.
Type ‘a’ then tab. F1_AfterUpdate fires. Jump to f2.
Tab. Jump to f4 (f3 is disabled).
Tab. Jump to f1.
Backspace to erase value in f1 (value is highlighted).
Tab. F1_AfterUpdate event fires. CURSOR DOES NOT MOVE TO NEXT FIELD. The next field would have been F2 but the event has disabled F2 and enabled F3.
Tab. Jump to F3. Why did it take 2 TAB hits to move?


Trial #2 – SETFOCUS
Open form. Activate event fires. Position at f1.
Tab. Jump to f2.
Tab. Jump to f3.
Tab. Jump to f4.
Tab. Jump to f1.
Type ‘a’ then tab. F1_AfterUpdate fires. Jump to F2.
Tab. Jump to F4 (f3 is disabled)
Tab. Jump to F1.
Backspace to erase value in f1 (value is highlighted)
Tab. F1_AFterUpdate fires. Jump to F3. This is better – in trial #1 the cursor did not move.
Tab. Jump to F4.
Tab. Jump to F1.
Enter ‘a’ then Tab. F1_AfterUpdate fires. Jump to F4 <---- WHAT? We should have gone to F2.

 

[Gammachaser]

I am not sure if I fully understand, but did you try putting the logic in the 'On Got Focus' event of the following control? That event fires whether the previous control was changed or not. For instance, I built 3 text boxes, txt0, txt1, txt2 (creative, yes?) and used the following code to redirect around:

Private Sub txt1_GotFocus()
If txt0 = "skip" Then txt2.SetFocus
End Sub

Private Sub txt2_GotFocus()
If txt1 = "SkipTo0" Then txt0.SetFocus
End Sub

What you have to be careful about is building a situation where none of the controls can take focus because each one tries to pass to the next in a loop. for instance, in the above example, if you put 'skip' in txt0, control immediately passes to txt2 (as it should) but if you then type 'skipto0' in txt2, you get an error "Can't move the focus to the control txt2" when you try to tab off of txt0 and execution stops. In your example, this should not be a problem.

 

[combo]

You could switch to 'Change' event and mimic enabling/disabling events for some controls using additional variable and condition.
The code from your post of 10 Jun 11 11:52 after modification (disabled controls do not fire events, so no need to use the bFormEventsOn in internal IF..EndIf blocks):

Option Explicit

Private bFormEventsOn As Boolean

Private Sub comboF1_Change()
If bFormEventsOn = True Then
    lbStack.AddItem "comboF1_AfterUpdate"
    If comboF1.ListIndex = -1 Then
        comboF2.Enabled = False
        comboF2.ListIndex = -1
        comboF3.Enabled = True
        comboF3.SetFocus
    Else
        comboF2.Enabled = True
        comboF3.Enabled = False
        comboF2.SetFocus
    End If
End If
End Sub

Private Sub combof3_Change()
If bFormEventsOn = True Then
    lbStack.AddItem "comboF3_AfterUpdate"
    If comboF3.ListIndex = -1 Then
        comboF1.Enabled = True
    Else
        comboF1.Enabled = False
    End If
End If
End Sub

Private Sub UserForm_Activate()
    bFormEventsOn = False
    lbStack.AddItem "UserForm_Activate"
    comboF1.AddItem "a"
    comboF2.AddItem "b"
    comboF3.AddItem "c"
    comboF4.AddItem "d"
    bFormEventsOn = True
End Sub

combo

 

[dean12]

Thank you to all who have responded to date. I worked on this over the weekend and came up with an approach of inserting an intermediate text control. So:

ComboF1
txtSwitch
ComboF2
ComboF3
ComboF4

ComboF1 sets the enabled property of F2 & F3. Then the tab order takes us to txtSwitch where I setup the ENTER event. In there I say:

If comboF2.enabled then
comboF2.setfocus
else
comboF3.setfocus
endif

I make txtSwitch invisible and it appears that this works ok. Again it seems like the problem is that the decision about where we are going to go next is made, by Excel, in ComboF1 early in the game and logic in the AfterUpdate event is not going to change things. I probably could have gotten away without the setfocus but I couldn't figure out how to get out of the txtSwitch control in an automatic fashion - I don't want the user to even know it is there.