Return active OLEObject in Worksheet

Re: Return active OLEObject in Worksheet

Why use ActiveX controls instead of the cells & Data validation lists?

Re: Return active OLEObject in Worksheet

You Can make use of Application.Onkeys.
Application.OnKey "{TAB}","KeyCapturing"
Application.OnKey "{ESC}","Rangeselect"

In the first on keys whenever a user press tab button the KeyCaputring macor will be called. and in case of second onkeys event rangeselect will be called.

I know this is a very very late response. But I wish it helps someone who stumbles upon this page with the same frustration.

Don't be put off by the length of what is written here. Not complicated like it appears. Simple to understand with a bit of patience. And it will save tons of time.

Solution: The way is to create two Class Modules (one for TextBox KeyDown event and one for ComboBox KeyDown event) and set all textboxes and comboboxes to these classes. The class module will pass the name of active oleObject to the subroutine you wish to run.

Also like Jonathon required, we could write a subroutine inside the keydown event of these class modules just once and that would apply to all textboxes and comboboxes in the worksheet. This saves the time of writing the same codes for KeyDown event of all textboxes and comboboxes separately.

The above classes would determine which oleObject is active and pass that to the subroutine you want to run using that information.

Solution Step by Step

STEP 1

I will first write the subroutine that finally runs - the one that controls tabbing between textboxes and comboboxes using the information about the active oleObject.

Assumed that array is already in place.

Here we loop through the array to find the position of active oleObject in the array and jump to the next oleObject in the array. The name of the active oleObject is passed as parameter to this subroutine by the class modules which we create.

Sub JumpToNextCtlfrmTxtBx(ActiveTxtBx As MSForms.TextBox)

    For i = LBound(objArray) To UBound(objArray)
        If objArray(i).Name = ActiveTxtBx.Name Then
            If i <> UBound(objArray) Then
                objArray(i + 1).Activate
            Else
                objArray(1).Activate
            End If
        End If
    Next i

End Sub

Sub JumpToNextCtlfrmComBx(ActiveComBx As MSForms.ComboBox)

    For i = LBound(objArray) To UBound(objArray)
        If objArray(i).Name = ActiveComBx.Name Then
            If i <> UBound(objArray) Then
                objArray(i + 1).Activate
            Else
                objArray(1).Activate
            End If
        End If
    Next i

End Sub

STEP 2

Create a Class Module for TextBox (Insert > Class Module) .

I am naming it as ClsTxtBx (Click on the created Class, press F4 and type the name in the box to the right of 'Name' in the new Properties window that pops up if you do not have one already.)

Inside the ClsTxtBx class module, type the following codes

Option Explicit

Public WithEvents TxtCtl As MSForms.TextBox

Now select TxtCtl from the dropdown list on the left top of the module.

Select the KeyDown event for the TxtCtl from the dropdown list on the right top of the module.

Type the following codes for the KeyDown event

Private Sub TxtCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
        JumpToNextCtlfrmTxtBx TxtCtl

all subroutines we place here will run for the keydown event of all textboxes.

End Sub

STEP 3

Create a Class Module for ComboBox (Insert > Class Module) .

I am naming it as ClsComBx (Click on the created Class, press F4 and type the name in the box to the right of 'Name' in the new Properties window that pops up if you do not have one already.)

Inside the ClsComBx class module, type the following codes

Option Explicit

Public WithEvents ComboCtl As MSForms.ComboBox

Now select ComboCtl from the dropdown list on the left top of the module.

Select the KeyDown event for the TxtCtl from the dropdown list on the right top of the module.

Type the following codes for the KeyDown event

Private Sub ComboCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
        JumpToNextCtlfrmComBx ComboCtl

all subroutines we place here will run for keydown event of all comboboxes.

End Sub

STEP 4

Place the following codes in the Open event of the workbook.

Dim TxtBxArr() As New ClsTxtBx, ComboBxArr() As New ClsComBx

Private Sub Workbook_Open()

    Dim i As Integer, ole As OLEObject

    For Each ole In Sheet1.OLEObjects

          If ole.progID = "Forms.TextBox.1" Then

              i = i + 1
              ReDim Preserve TxtBxArr(1 To i)
              Set TxtBxArr(i).TxtCtl = ole.Object

          End If

          If ole.progID = "Forms.ComboBox.1" Then

              i = i + 1
              ReDim Preserve ComboBxArr(1 To i)
              Set ComboBxArr(i).ComboCtl = ole.Object

          End If

    Next ole

End Sub