AfterUpdate/BeforeUpdate and SetFocus

[odekkers]

I have some problems with the SetFocus command in a Userform with an
AfterUpdate or BeforeUpdate routine.

To make it clear to you, I made a very small Userform to demonstrate it. It
only contains 8 TextBoxes and this code:

Code:
Private Sub TextBox01_AfterUpdate()
Me.TextBox08.SetFocus
End Sub
-
Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox08.SetFocus
End Sub
-
Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox08.SetFocus
End Sub
-
Private Sub TextBox04_Change()
TextBox08.SetFocus
End Sub

I expected that in all cases, the focus should be set to TextBox08, but this
only happens with the Change event.

Do you have any suggestions how I can set the focus to TextBox08 in an
AfterUpdate or BeforeUpdate event?

I use Excel 2007

Kind regards,

Otto.

 

[OssieMac]

Hi Otto,

I believe that the problem you face is when the event takes place. Other
than change, the cursor has already moved to the next control before the
event fires. The change event takes place as soon as any change is made and
not necessarily after all changes have been made within the control. Example
change text to pest; change event takes place as soon as you type the p.

The following is one method of achieving your desired result. I am
interested in other solutions so I'll monitor this thread for any.

On the enter event sets all the tab stops to False and then sets the tab
stop of the desired next control to True. I have used 8 Text boxes and set
the tab stop to a different text box on each of the Enter events.

Private Sub TextBox01_Enter()
Call TabStopsFalse
Me.TextBox05.TabStop = True
End Sub

Private Sub TextBox02_Enter()
Call TabStopsFalse
Me.TextBox06.TabStop = True
End Sub

Private Sub TextBox03_Enter()
Call TabStopsFalse
Me.TextBox07.TabStop = True
End Sub

Private Sub TextBox04_Enter()
Call TabStopsFalse
Me.TextBox08.TabStop = True
End Sub

Sub TabStopsFalse()
Dim ctrl As Control
For Each ctrl In Controls
ctrl.TabStop = False
Next ctrl
End Sub