Combo box - data validation drop down help

Question

I used a combo box and vba code from instructions I found in order to have my data validation with drop down list autocomplete as I start typing. The code was pasted to the sheet's vba (pasted below). This method converts all data validation lists on the sheet to autocomplete as the combo box. I only want the combo box/auto fill to apply to one specific data validation list. Please help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Update by Extendoffice: 2020/01/16

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("TempCombo") 

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.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(8, 0).Activate 

End Select 

End Sub

Answer 1

Hi Mike G!

To apply the combo box/auto-fill to only one specific data validation list, you need to modify the code so that it only runs for that specific range.

First, you need to change the line "If Target.Validation.Type = 3 Then" to specify the range you want to apply the code to.

For example, if you want to apply it to cells B2:B10, you would change it to:

If Not Intersect(Target, Range("B2:B10")) Is Nothing And Target.Validation.Type = 3 Then

This line will check if the current selection is within the specified range and has a data validation list.

Then, you need to change the line "Set xCombox = xWs.OLEObjects("TempCombo")" to specify the name of the combo box you want to use for this specific range. For example, if the name of the combo box is "Combo1", you would change it to:

Set xCombox = xWs.OLEObjects("Combo1")

Finally, you need to modify the line that sets the LinkedCell property of the combo box to use the address of the first cell in the range, rather than the address of the current selection. For example, if the range is B2:B10, you would change it to:

. LinkedCell = Range("B2"). Address

After making these changes, the code will only apply the combo box/auto fill to the specified range.

Kind Regards, Shakiru

Answer 2

Change the line

If Target.Validation.Type = 3 Then

to

If Target.Address = "$D$3" Then

where D3 is the cell for which you want the drop down with AutoComplete.