combobox searchable

[ZTK]

Again requesting your help .......

I found a macro for drop-down lists, it works perfect for me as it creates the combobox in any "simple" drop-down list so there is no need to copy it manually.


I would like the combobox to have the search function, to filter the possible texts to use.

I show you the macro I use:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Update by Extendoffice: 2020/05/19

Dim xCombox As OLEObject

Dim xStr As String

Dim xWs As Worksheet

Dim xArr



Set xWs = Application.ActiveSheet

On Error Resume Next

If Target.Validation.Type = 3 Then

Target.Validation.InCellDropdown = False

Cancel = True

xStr = Target.Validation.Formula1

xStr = Right(xStr, Len(xStr) - 1)

If xStr = "" Then Exit Sub

Set xCombox = xWs.OLEObjects("TempCombo")

With xCombox

.ListFillRange = ""

.LinkedCell = ""

.Visible = False

End With

With xCombox

.Visible = False

.Left = Target.Left

.Top = Target.Top

.Width = Target.Width + 3

.Height = Target.Height + 3

.ListFillRange = xStr

If .ListFillRange = "" Then

xArr = Split(xStr, ",")

Me.TempCombo.List = xArr

End If

.LinkedCell = Target.Address

End With

xCombox.Activate

Me.TempCombo.DropDown

End If

End Sub

Private Sub TempCombo_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




thanks in advance

 

[Akuini]

Instead of data validation it's better to use combobox for a searchable data entry process.

Here's an example of a searchable combobox in a single cell:
deCombobox - sheet,1 cell, searchable,filter - example 1.xlsm

If you want a searchable combobox in multiple cells, you can find an example here:
best-way-to-create-a-searchable-drop-down-list-with-auto-complete-functionality-to-cells-in-a-column
check post #2 for explanation & post #109 to download the latest version of the sample workbook.

The code is a bit complex but it's easy to set it up.
Let me know if you're interested in this method.

 

[ZTK]

Gracias por la pronta respuesta.

Por supuesto, si estoy interesado, especialmente necesito que el archivo tenga un cuadro combinado en algunas celdas y, como se agregan filas de la misma manera, se agregan listas desplegables.Las

celdas varían, no están en una sola columna, yo sí. No sé si me entienden o hay una forma de adjuntar mi expediente.

 

[Fluff]

@ZTK please post to this board in English only. If you wish to post in your native language then you need to post your thread in the Questions in Other Languages section.
Thanks

 

[ZTK]

@Fluff sorry.


Thanks for the prompt response.

Of course if I'm interested, I especially need the file to have a combo box in some cells and since rows are added in the same way, dropdown lists are added

. I don't know if they understand me or is there a way to attach my file.

Instead of data validation it's better to use combobox for a searchable data entry process.

Here's an example of a searchable combobox in a single cell:
deCombobox - sheet,1 cell, searchable,filter - example 1.xlsm

If you want a searchable combobox in multiple cells, you can find an example here:
best-way-to-create-a-searchable-drop-down-list-with-auto-complete-functionality-to-cells-in-a-column
check post #2 for explanation & post #109 to download the latest version of the sample workbook.

The code is a bit complex but it's easy to set it up.
Let me know if you're interested in this method.

 

[Akuini]

is there a way to attach my file.

You could upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive. And then share the link here.

 

[ZTK]

You could upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive. And then share the link here.

very well, I uploaded my file to google drive, this is where I want to adapt the search macro.

BITX.xlsm

drive.google.com

Thank you again

 

[Akuini]

I uploaded my file to google drive

The link gives me Access Denied.

 

[ZTK]

The link gives me Access Denied.

Apologies!!!

Ready, updated

BITX.xlsm

drive.google.com

 

[Akuini]

In your file I can see that you use merged cell almost on whole data range !
Merged cell won't work well with macro, so it's your choice, if you want to apply the searchable combobox as I suggested then you need to remove the merge cells.
Instead of merging cells, you should use "center across selection":

MS Excel 2016: Center text across multiple cells

This Excel tutorial explains how to center text across multiple cells in Excel 2016 (with screenshots and step-by-step instructions). I want to center text across multiple cells in Microsoft Excel 2016 but I don't want to have to merge the cells. How can I do this?

www.techonthenet.com

but actually where do you want to apply the searchable combobox ?