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.
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
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
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 4
.Height = Target.Height + 4
.LinkedCell = Target.Address
ListFillRange = StrX
Private Sub CCTemp_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
I found several suggestions on the web but none of them worked for me.
Any ideas? Thanks a lot!
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)