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

 

[ZTK]

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 ?

Yes, just yesterday I saw a post of yours making the recommendation of the combined cells; I'll modify that as soon as I get a chance.

I want to apply the search combo boxes in the sections with the headings:

UBICACION, Activación de, Área, Puesto, Juatificacion at the end, in OP SÍS ENTREGA, SUPERVISA OP SÍS RECIBE


The macro I have, replaces all the drop down lists so I have no problem if rows are added

The detail is the search for the combo box

 

[Akuini]

I'll modify that as soon as I get a chance.

Ok, I'll wait for the file.

 

[ZTK]

Ok, I'll wait for the file.

sorry for the delay.

I add the formatting already with the cells corrected.

Thank you again

BITX.xlsm

drive.google.com

 

[Akuini]

Ok, I've downloaded your file.
Can you explain where do you want to apply the combobox also where the list for each combobox is located?

 

[ZTK]

Of course

The combobox go in the header cells UBICACION, MOTIVO POR EL QUE SE REALIZA BYPASS, Activacion de, Área, Puesto, Justificación. SSS, Just and finally OP1, OP1 and SUPERVISA

Thanks for all

 

[ZTK]

The list are in BD

For the moment and in order not to complicate it, it can be linked to any list, later I modify it to the needs since they may change

The detail is to have a general idea to understand and work the macro

 

[Akuini]

Ok, try this:
I apply the combobox in cells "Z53:Z54,AL53:AL54" (highlighted yellow) and the source are in col D,J sheet BD consecutively. It maybe a wrong column but it's just an example on how to connect between the cells where the combobox is located & its source. You can add more cells & source to apply the combobox.

Here's how to set it up:
-On your workbook, create an activex combobox (its name should be ComboBox1) in the sheet where you want to enter data with the combobox (say "sheet1"), you can put it anywhere. Also create an activex commandbutton (change the caption to "ON-OFF" or anything)
-Put the list (as the source of the combobox) in another sheet col A.
-Copy the macro in sheet1 in this sample workbook.
-Back to your workbook, right click the sheet1 name tab and choose "View Code" then paste the macro.

Note: you don't need the data validation.
-You need to adjust some part of the code in this part:

'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART:
'sheet's name where the list (for combobox) is located.
Private Const sList As String = "BD"

'list (in sheet "BD") starts at row:
Private Const nRow As Long = 2

'column where the list is located (in sheet "BD")
Private Const mCell As String = "4,10" 'it means col D,J

'range where you want to use the combobox
Private Const xCell As String = "Z53:Z54,AL53:AL54"

How to use:
- Click the ON-OFF button to activate the setup. You can turn the combobox on and off.
- Selecting a cell in yellow area will activate the combobox
- type some keywords to search, e.g "f ca"
- use up-down arrow to select an item then hit ENTER
(the selected item will be inserted into the cell)
- to leave the combobox: hit TAB or ESC
- to delete 1 cell content: select the cell then hit ENTER (while combobox is empty)
- to delete more than 1 cell content: select the cells (it won't activate the combobox) then delete
Note: selecting more than 1 cell won't activate the combobox

The file:

BITX 2.xlsm

Shared with Dropbox

www.dropbox.com

You can change some of the combobox property in this part:

 'setting up combobox property
        With ComboBox1
        .Height = Target.Height + 15
        .Width = Target.Width + 55
        .Top = Target.Top - 2
        .Left = Target.Offset(0, 1).Left
        .Visible = True
        .Value = ""

 

[ZTK]

Ok, try this:
I apply the combobox in cells "Z53:Z54,AL53:AL54" (highlighted yellow) and the source are in col D,J sheet BD consecutively. It maybe a wrong column but it's just an example on how to connect between the cells where the combobox is located & its source. You can add more cells & source to apply the combobox.

Here's how to set it up:
-On your workbook, create an activex combobox (its name should be ComboBox1) in the sheet where you want to enter data with the combobox (say "sheet1"), you can put it anywhere. Also create an activex commandbutton (change the caption to "ON-OFF" or anything)
-Put the list (as the source of the combobox) in another sheet col A.
-Copy the macro in sheet1 in this sample workbook.
-Back to your workbook, right click the sheet1 name tab and choose "View Code" then paste the macro.

Note: you don't need the data validation.
-You need to adjust some part of the code in this part:

'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART:
'sheet's name where the list (for combobox) is located.
Private Const sList As String = "BD"

'list (in sheet "BD") starts at row:
Private Const nRow As Long = 2

'column where the list is located (in sheet "BD")
Private Const mCell As String = "4,10" 'it means col D,J

'range where you want to use the combobox
Private Const xCell As String = "Z53:Z54,AL53:AL54"

How to use:
- Click the ON-OFF button to activate the setup. You can turn the combobox on and off.
- Selecting a cell in yellow area will activate the combobox
- type some keywords to search, e.g "f ca"
- use up-down arrow to select an item then hit ENTER
(the selected item will be inserted into the cell)
- to leave the combobox: hit TAB or ESC
- to delete 1 cell content: select the cell then hit ENTER (while combobox is empty)
- to delete more than 1 cell content: select the cells (it won't activate the combobox) then delete
Note: selecting more than 1 cell won't activate the combobox

The file:

BITX 2.xlsm

Shared with Dropbox

www.dropbox.com

You can change some of the combobox property in this part:

 'setting up combobox property
        With ComboBox1
        .Height = Target.Height + 15
        .Width = Target.Width + 55
        .Top = Target.Top - 2
        .Left = Target.Offset(0, 1).Left
        .Visible = True
        .Value = ""

Thank you so much

I check it and modify it with more real data

I hope I don't disturb anymore

 

[ZTK]

I already downloaded the file, but when "turning on" the macros it gives me the following error:

Set dar = CreateObject ("System.Collections.ArrayList")

Is there any solution?

THANKS

 

[Akuini]

What was the error message?
See if this helps;

automation error -2146232576 (80131700) on creating an array

I have a macro that's working on one computer just fine, however when I try to run it on another computer I get the following error: Run-time error '-2146232576 (80131700)' Automation error Here'...

stackoverflow.com