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]

You're welcome, glad to help & thanks for the feedback.

Hello again

Back with another annoyance, the format has already been implemented in my work, now there are minimal details about it; one of them is:

Is there a way to modify the macro so that the combobox takes the selected value when clicking on another cell? (as if pressing enter or tab)

Again thank you very much

 

[Akuini]

Could you upload your last version file so I can test it?

 

[ZTK]

Could you upload your last version file so I can test it?

Apologies for taking time to reply

I have already managed to adapt them to the form that the file is taking and it is getting better and better ...

I have realized that to copy and paste the cell that has the combobox in another sheet it is impossible

It occurs to me, is there a way that only the combobox is activated when scrolling with the up and down keys? Not that the list is automatically displayed

And another request please, what must be modified so that I can "pass" the combobox cell with the left or right keys? As with the ESC key

THANK YOU SO MUCH