ActiveX ComboBox ListFillRange

[Delarc]

On Sheet1 I have a TestTable with a 400 row column.
In Sheet2 I want an ActiveX ComboBox that is filled with the data from TestTable.
Using a macro, I try to complete its ListFillRange Property, but it can't.

 

[Delarc]

In a Sheet I have a Excel Table called "TblBolígrafos" with several columns and hundreds of rows.
I need to refer to this Table from different Sheets of my Workbook.
Rows are constantly being added or removed from this Table

I defined a Range "Escritura" that includes all the rows of the first column

To complete with a "Escritura" value, a Cell of any Sheet of the Book, I validate it with a List:

So far it works fine.
But since the List is very large I thought to use an ActiveX ComboBox to take advantage of its MatchEntry Property.
I named the ComboBox "CCTemp"

I added this VBA code, but it only works fine if the ComboBox is on the Sheet where the Table is.
If it is in another Sheet, the code does NOT give an Error but neither does it fill with the "Escritura" data.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim Ws As Worksheet
    Dim ComboX As OLEObject
    Dim StrX As String
    
    On Error Resume Next
    Set Ws = Application.ActiveSheet
    Set ComboX = Ws.OLEObjects("CCTemp")

    With ComboX 'Oculta el Combo ActiveX
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With

    If Target.Validation.Type = 3 Then 'Si Validación es tipo Lista
        Target.Validation.InCellDropdown = False
        Cancel = True
        StrX = Target.Validation.Formula1
        StrX = Right(StrX, Len(StrX) - 1) 'quita el signo =
        If StrX = "" Then Exit Sub

        With ComboX
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 4
            .Height = Target.Height + 4
            .LinkedCell = Target.Address
            '----------AQUI FALLA-----------
            ListFillRange = StrX
            '--------------------------------
        End With
        ComboX.Activate
        Me.CCTemp.DropDown
    End If

End Sub
Private Sub CCTemp_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
    
End Sub

I found several suggestions on the web but none of them worked for me.
Any ideas? Thanks a lot!

 

[GWteB]

I think converting your table column to a "real" address should work:

    Dim r As Range
    
    Set r = ThisWorkbook.Names("Escritura").RefersToRange
    ComboX.ListFillRange = "'" & r.Parent.Name & "'!" & r.Address

 

[Akuini]

It looks like you want to set up a searchable combobox + data validation to enter data.

Here's another option to consider:
You can use a userform + searchable listbox to enter data. The userform appears when you double-click a cell in a specified range. The search ignores the keywords order, so the keyword "ma la" would match "Maryland" and "Alabama".
How it works:
- Double-click a cell in blue area to open the userform.
- In the texbox type some keywords to search, e.g "ma la"
- Use Down Arrow or Tab to move the cursor to the Listbox.
- Hit Enter or double-click to insert the selected data into the active cell.
- To continue entering data to the next cell press Alt+Down arrow OR hit NEXT button (or press Alt+Up arrow OR hit PREV button to enter data in the previous cell)

The workbook:
Data_Entry_-_Useform-searchable_Listbox-1_column.xlsm

 

[Delarc]

Hello friend
Both solutions work perfect for me and I am going to implement them in different sectors of my projec
Many thanks to both: GWteB and Akuini

 

[GWteB]

You are welcome and thanks for letting us know.