ActiveX ComboBox ListFillRange

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
1610836231606.png


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


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

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"

1610837449241.png

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.
VBA Code:
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!
 

Attachments

  • 1610832296009.png
    1610832296009.png
    12.8 KB · Views: 0
  • 1610832720641.png
    1610832720641.png
    33.2 KB · Views: 0
  • 1610833103456.png
    1610833103456.png
    49.2 KB · Views: 0
  • 1610834482833.png
    1610834482833.png
    13.3 KB · Views: 0

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I think converting your table column to a "real" address should work:

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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)

data entry via listbox.jpg


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

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting us know.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top