Problem with searchable combo box

[EtienneD]

Hi everybody. Here is my problem, I am using a combo box for my dropdown list with multiple data validation and the list appears blank and I don't know why. Here is the code I have used:
*note that the data comes from another sheet*
*note that the data list for the dropdown are not placed vertically, they are left to right*

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If

exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
'Table with numbers for other keys such as Right Arrow (39)
'Key Code Constants

Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================

 

[Akuini]

If you're interested, here's an example of a searchable drop-down:

Macro to create searchable data validation+combobox

Akuini submitted a new Excel article: Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the...

www.mrexcel.com

OR

Another option:
I created an Excel add-in for searchable data validation (with combobox) , called "Search deList".
Its function is to speed up the search in the data validation list. It works on multiple cells. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.
It works on any cells that have data validation on any workbook, and you don't need VBA for this.
I share this add-in here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

 

[EtienneD]

If you're interested, here's an example of a searchable drop-down:

Macro to create searchable data validation+combobox

Akuini submitted a new Excel article: Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the...

www.mrexcel.com

OR

Another option:
I created an Excel add-in for searchable data validation (with combobox) , called "Search deList".
Its function is to speed up the search in the data validation list. It works on multiple cells. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.
It works on any cells that have data validation on any workbook, and you don't need VBA for this.
I share this add-in here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

Thanks for your response. I actually tried downloading your version B (automatic), but the code seem to not work properly.

 

[Akuini]

I actually tried downloading your version B (automatic), but the code seem to not work properly.

1. Are you saying that the sample workbook doesn't work? Could you elaborate?
2. Could you upload your workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[EtienneD]

1. Are you saying that the sample workbook doesn't work? Could you elaborate?
2. Could you upload your workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

Maybe I did something wrong, I will let you judge. Here is the link to the google drive:

EMPL_Fiche client_v1.2.xlsm

drive.google.com

The code is for the Sheet 1 (named TÂCHES COURANTES). The data comes from Sheet 3 (named LISTES) and are all dependent drop list so be careful not to change the source. In the VBA developer section there is 2 codes at the bottom that needs to stay the same (Private Sub Worksheet_Change(ByVal Target As Range) AND Sub SelectAll_Click() ). The 2 other ones on top were for the searchable combo box and can be modified.

Thank you so much for helping

 

[EtienneD]

Maybe I did something wrong, I will let you judge. Here is the link to the google drive:

EMPL_Fiche client_v1.2.xlsm

drive.google.com

The code is for the Sheet 1 (named TÂCHES COURANTES). The data comes from Sheet 3 (named LISTES) and are all dependent drop list so be careful not to change the source. In the VBA developer section there is 2 codes at the bottom that needs to stay the same (Private Sub Worksheet_Change(ByVal Target As Range) AND Sub SelectAll_Click() ). The 2 other ones on top were for the searchable combo box and can be modified.

Thank you so much for helping

I sent the wrong link, here is the good one:

Tâches courantes_V1.2.xlsm

drive.google.com

 

[Akuini]

Try this:

Tâches courantes_V1.2 #1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

1. I've changed the combobox name to "Combobox1".
2. The original code won't work because of Indirect function in dependent data-validation.
3. In the code, I refer to the cells with dependent data validation in col C (DESCRIPTIONS) in this part:

Private Sub ComboBox1_GotFocus()
Dim x
    
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .Value = ""
        .Clear
            Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
            
            If Not Intersect(ActiveCell, Range("C5:C13")) Is Nothing Then  '"C5:C13": cells with data validation in col C (DESCRIPTIONS)
                  vList = Evaluate(Replace(ActiveCell.Offset(, -1), " ", "_"))
            Else
                  vList = Evaluate(ActiveCell.Validation.Formula1)
            End If

 

[EtienneD]

Try this:

Tâches courantes_V1.2 #1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

1. I've changed the combobox name to "Combobox1".
2. The original code won't work because of Indirect function in dependent data-validation.
3. In the code, I refer to the cells with dependent data validation in col C (DESCRIPTIONS) in this part:

Private Sub ComboBox1_GotFocus()
Dim x
   
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .Value = ""
        .Clear
            Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
           
            If Not Intersect(ActiveCell, Range("C5:C13")) Is Nothing Then  '"C5:C13": cells with data validation in col C (DESCRIPTIONS)
                  vList = Evaluate(Replace(ActiveCell.Offset(, -1), " ", "_"))
            Else
                  vList = Evaluate(ActiveCell.Validation.Formula1)
            End If

That is awesome thank you. Any way to activate with a single click instead of a double click? If it's an easy writing I can do the switch.

 

[EtienneD]

That is awesome thank you. Any way to activate with a single click instead of a double click? If it's an easy writing I can do the switch.

Nevermind, it works perfectly like that! Thank you again

 

[Akuini]

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