How to determine next tabstop in VBA UserForm?

I have a UserForm with some textbox entry fields on it that are enabled/disabled by a checkbox. Whe开发者_如何学运维n a checkbox is clicked to check it, I'd like to move the focus into the now-enabled textbox.

The textbox is the next control after the checkbox in the tab order, so it seems like using the tab order to find the appropriate textbox would be a good idea.

But... how can I find the next control in the tab order after a given control? Is there a method to do that, or do I have to enumerate all the controls and figure it out for myself?

I appreciate that this comes under the heading of "enumerating all the controls" but it's pretty simple and I attach the code for completeness:

Private Sub CheckBox1_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.TabIndex = Me.ActiveControl.TabIndex + 1 Then
            ctl.SetFocus
            Exit For
        End If
    Next
End Sub

As a different way of looking at this.

Can you not rather use the textbox you want focussed, and set that name in the checkbox.tag

then in you vba code use

DoCmd.GoToControl CheckBox.Tag

Where the CheckBox.Tag is the Textbox.Name?

EDIT:

OK, I found this

SendKeys "{Enter}", True

at VBA code for moving to next control? It must be eeeasy

I had trouble with

SendKeys "{Enter}", True

With a little experimentation I found this works

SendKeys "{TAB}", True

One caveat...if you're in the VBE stepping through the code, and watching it on the form, SendKeys is executed is executed in the code. Confused the heck out of me at first why my code started to look odd, e.g. extra spacing and extra lines!

With all due respect to the kind advice offered thus far, this issue shouldn't be addressed through programming when there's a way to address it within the userform itself.

Click on the userform itself, then right click and select "tab order". You can then move each element within the userform to whatever position you want without having to resort to complicated and unstable programming tricks.