WS ActiveX TabOrder code


0. Quick guide - add a TabOrder feature to WS ActiveX controls


In this module:

  1. VBA code - use the VBA KeyDown event to detect use the keyboard Tab key
  2. The KeyDown event is part of a sequence:
    1. KeyDown
    2. KeyPress
    3. KeyUp

1. WS control TabOrder


The worksheet shown in figure 1 includes two text box (ActiveX controls). TabOrder in not included in the properties for worksheet controls. The procedure in code 1 provides this missing functionality.


textbox tab
Fig 1: - WorkSheet controls :: ActiveX TextBox x 2 and Label

2. VBA code


2.1 KeyDown event


Code 1 uses the KeyDown event for the vbKeyTab constant fired by the Tab key.



Code 1: TabOrder KeyDown event - WorkSheet code module 
Option Explicit

' Contents
' 1. Name: txtBox1; Type: Text Box (ActiveX Control)
' 2. Name: txtBox2; Type: Text Box (ActiveX Control)
' 3. Name: lblDemo; Type: Label (ActiveX Control)
' ===========================

' 1. TextBox upper - TabOrder
Private Sub txtBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then
        txtBox2.Activate
    End If
End Sub

' 2. TextBox upper - TabOrder
Private Sub txtBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then
        txtBox1.Activate
    End If
End Sub

KeyDown can be applied to:


2.2 Activate the first control



Code 2: Worksheet Activate event - WorkSheet code module 
' Set insertion point in txtBox1
Private Sub Worksheet_Activate()
    txtBox1.Activate
End Sub

2.3 Fine tune the Label control



Code 3: Label control Click event - WorkSheet code module 
' 3. Label
Private Sub lblDemo_Click()
    ' run once only
Dim DblNewLine As String
DblNewLine = String(2, vbNewLine)
    With lblDemo
        .Caption = DblNewLine & "TextBox TAB ORDER demo"
        .BackColor = Range("xlfHolder").Interior.Color
    End With
End Sub

References