Autocomplete When Typing Drop Down List Or create Drop Down List with Autocomplete Option dependent drop downlist

[haroune]

Hello guys ,

good day to you all , much appreciate this space


in data validation i have a dynamic range i am using indirect formula by defining name to range this method is not picking up the formula how can i make this dynamic so i can update my list easily ( ie : i have list 1 of Vendors , and list 2 for their respective contacts dependent on the 1st list ) so in order to add a feature of possibility to type a new Vendor or an updated contact , i am using this code :
liste 1 : from data validation = Vendors
list 2 : from data validation : =INDIRECT(select the cell of Vendors)

and i want to know why this code is not working on the dependent list ( it works fine on all dropdown lists expect for those who have INDIRECT formula ) and does it work on merged cells ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("DropListTemp")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
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
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.DropListTemp.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.DropListTemp.DropDown
End If
End Sub
Private Sub DropListTemp_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