excel 2013 vba: Custom "tab" button not making continuous cycle through textboxes

[jedwardo]

Hello Everyone,

I've created this button on a userform on-screen keyboard to cycle through all the textboxes on another userform. After the 21st textbox (21st by TabIndex) it returns to the first textbox and starts over. What's happening though is that after making it through 22 boxes it stops cycling and does nothing at all. If I then call up the on-screen keyboard again (without resetting the userform it's being used with) it will cycle through 21 textboxes, then 20, then 19 and so on until I reset UserFormContacts and then it starts over at 22 again.

I'm pretty sure it's got something to do with the Case 22 since it cycles through 22 textboxes the first go around but I don't know what to change.

Here's the code:

Public iTab As Long

Private Sub cbnTab_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim ctl As Control
        For Each ctl In UserFormContacts.Controls
            If ctl.TabIndex = iTab + 1 Then
                Select Case ctl.TabIndex
                    Case 22
                        ' Wrap focus to the first textbox
                        UserFormContacts.TextBoxFirstName.SetFocus
                        Exit Sub
                    Case Else
                        ctl.SetFocus
                        Exit Sub
                End Select
            End If
        Next
End Sub

Everytime the tab button is clicked it sets focus on the next TextBox Enter event which sets iTab to the ActiveControl.TabIndex. All the Textboxes look like this one below:

Private Sub TextBoxAddress_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    iTab = ActiveControl.TabIndex
        If Me.TextBoxAddress.Name = Me.ActiveControl.Name Then
            FullKeyboard
        Else
            Exit Sub
        End If
End Sub

 

[mikerickson]

In the Enter event, have you tried using the control itself rather than ActiveControl to set iTab

Private Sub TextBoxAddress_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    iTab = TextBoxAddress.TabIndex: Rem <<<<<<<<<<<<<<<<<<<<<<<
        If Me.TextBoxAddress.Name = Me.ActiveControl.Name Then
            FullKeyboard
        Else
            Exit Sub
        End If
End Sub

 

[jedwardo]

Changed all 22 of the textboxes as in above code but still same results.

 

[mikerickson]

If it stops working at a particular control, then at some point, no control meets the condition .TabIndex = iTab + 1

Try changing the condition to

        For Each ctl In UserFormContacts.Controls
    
            If ctl.TabIndex =(iTab Mod UserFormContacts.Controls.Count) + 1 Then
                If ctl.TabStop Then
                    Select Case ctl.TabIndex
                        Case 22
                            ' Wrap focus to the first textbox
                            UserFormContacts.TextBoxFirstName.SetFocus
                            Exit Sub
                        Case Else
                            ctl.SetFocus
                            Exit Sub
                    End Select
                Else
                    iTab = (iTab Mod UserFormContacts.Controls.Count) + 1
                End If
            End If
        Next

 

[jedwardo]

Thanks, I'll give this a go when I get home today

 

[jedwardo]

It's cycling well now, thanks Mike.