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

 

[DanteAmor]

Maybe it's the version.

 

[Akuini]

@ZTK
Sorry for the late reply.

AND CAN THE ON / OFF BUTTON BE DELETED AND LEAVE THE MACRO ENABLED AT ALL TIMES?

You only need to press the ON-OFF button once, that’s when you open the workbook, after that the combobox macro will be enabled all times.
If you want I can amend the code so when you open the workbook it is already enabled.
The reason why I put the ON-OFF button is in case you need to edit the cells (in the defined range) without invoking the combobox.

WHEN ADDING MORE ROWS BETWEEN ANY HEADER WILL THERE BE A WAY THAT THE COMBOBOX ARE ALSO ADDED AUTOMATICALLY? WITHOUT HAVING TO MODIFY THE RANGE IN THE MACRO

There's a version that does that, but it uses both data validation+combobox. If you're ok with that I can share you an example.

 

[ZTK]

@ZTK
Sorry for the late reply.

You only need to press the ON-OFF button once, that’s when you open the workbook, after that the combobox macro will be enabled all times.
If you want I can amend the code so when you open the workbook it is already enabled.
The reason why I put the ON-OFF button is in case you need to edit the cells (in the defined range) without invoking the combobox.


There's a version that does that, but it uses both data validation+combobox. If you're ok with that I can share you an example.

Do not worry

I appreciate the answer, and yes

Would you help me with the example with the macro always on (without the button) and with the option to add more rows in multiple headers to append data

I thank you

 

[Akuini]

the example with the macro always on (without the button) and with the option to add more rows in multiple headers to append data

Try:

COMBOBOX+DAVAL-searchable,dict+arraylist,automatic,no button,formula-1.xlsm

Shared with Dropbox

www.dropbox.com

 

[Akuini]

Note:
In the file on post #44:
The code will use data validation (with List type) to activate the combobox.
Don't worry about duplicate & blanks on the list (if there's any). In the combobox, the list will be unique & sorted & no blank.
If your list is dynamic then - in the formula - specify the range up to the maximum possible data row. Say now you have the list in A2:A100, but it's possible to expand up to A200, then you can set the range to $A$2:$A$200.

 

[ZTK]

Note:
In the file on post #44:
The code will use data validation (with List type) to activate the combobox.
Don't worry about duplicate & blanks on the list (if there's any). In the combobox, the list will be unique & sorted & no blank.
If your list is dynamic then - in the formula - specify the range up to the maximum possible data row. Say now you have the list in A2:A100, but it's possible to expand up to A200, then you can set the range to $A$2:$A$200.

I am very thankfull for your help

I will test it and adapt it to my needs

Excellent ??

 

[ZTK]

Note:
In the file on post #44:
The code will use data validation (with List type) to activate the combobox.
Don't worry about duplicate & blanks on the list (if there's any). In the combobox, the list will be unique & sorted & no blank.
If your list is dynamic then - in the formula - specify the range up to the maximum possible data row. Say now you have the list in A2:A100, but it's possible to expand up to A200, then you can set the range to $A$2:$A$200.

First of all thanks for the example file, with that help and that of DanteAmor I have assembled my file to measure ...

Just a query, is there a way to adapt the macro for cells combined in a "traditional" way (using the combine and center button)? Since for the needs of my file they must be that way and not as you had previously suggested.

I really appreciate the support

 

[Akuini]

Just a query, is there a way to adapt the macro for cells combined in a "traditional" way (using the combine and center button)? Since for the needs of my file they must be that way and not as you had previously suggested.

I don't understand what you mean, can you explain in more detail?

 

[ZTK]

I don't understand what you mean, can you explain in more detail?

Just like post # 9 in the first file I shared had advised me not to use cell merge

But by necessity if I should use some in this way

 

[Akuini]

Well, if you still want to use the merge cells then remove (or comment) the blue lines in this part:

'=================================================================================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Target.Cells.CountLarge = 1 Then
    vList = Empty
    If isValid(Target) Then 'if activecell has data validation type 3
        On Error GoTo skip
        Set xRange = Evaluate(Target.Validation.Formula1)
            Call toShowCombobox
    Else
        If ComboBox1.Visible = True Then ComboBox1.Visible = False
    End If
'End If

Exit Sub
skip:
If ComboBox1.Visible = True Then ComboBox1.Visible = False

End Sub