[Excel VBA] How to Always Show the Full Dropdown List (Resetting Validation)

Using dropdown lists in Excel is a common way to simplify data entry. However, you may encounter issues where the list behavior changes or feels restricted after a selection is made.

This article explains how to use VBA (Visual Basic for Applications) to force a dropdown list to always display the full list of candidates, ensuring a consistent user experience every time a cell is clicked.

Basic Method: Setting a Dropdown List in Excel

Standard dropdown lists are created using the “Data Validation” feature.

1. Prepare the List: Enter the items you want to use as candidates in a cell range (e.g., A1:A10).
2. Select the Cell: Click the cell where you want the dropdown to appear (e.g., D1).
3. Set Data Validation: Go to the Data tab, click Data Validation, and select List.
4. Enter Source: In the “Source” field, enter the range of your candidate list (e.g., =$A$1:$A$10) and click OK.

This sets up a standard list. However, to ensure the list remains robust and fully populated regardless of previous interactions, we can use the VBA method described below.

Implementing a Persistent Full List with VBA

The following VBA code automatically resets the dropdown list in cell D1 to show all candidates whenever a change occurs. This ensures that the full list is always available for the next selection.

VBA Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim fullList As String
    
    ' Set the worksheet and range for the candidate list
    Set ws = ThisWorkbook.Sheets("CandidateListSheet") ' Change to the name of the sheet with your list
    Set rng = ws.Range("A1:A10") ' Change to the range of your list
    
    ' Do not process if the change is not in cell D1
    If Target.Address <> "$D$1" Then Exit Sub
    
    ' Generate the full candidate list string
    fullList = ""
    For Each cell In rng
        If cell.Value <> "" Then
            fullList = fullList & cell.Value & ","
        End If
    Next cell
    
    ' Remove the trailing comma
    fullList = Left(fullList, Len(fullList) - 1)
    
    ' Always display the full list in the dropdown
    With Target.Validation
        .Delete ' Delete existing validation and re-set it
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=fullList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = False
    End With
End Sub

Explanation of the Code

1. Define the List Range: The lines Set ws = ... and Set rng = ... specify where the source data is located. You should adjust these to match your actual sheet name and cell range.
2. Target Condition: The line If Target.Address <> "$D$1" Then Exit Sub ensures that the code only runs when cell D1 is changed. This prevents the macro from running unnecessarily when you edit other parts of the sheet.
3. Generate the Full List: The code loops through the source range (rng), combines the values into a single string separated by commas, and removes the final trailing comma. This string (fullList) is used as the source for the validation.
4. Update the Dropdown: The With Target.Validation block deletes any existing validation rules on the cell and immediately re-applies the list using the fullList string. This guarantees the dropdown is fresh and complete.

Summary

By customizing the dropdown behavior with VBA, you can improve the usability of your Excel forms. This method is particularly useful when you need to ensure the integrity of the selection list is maintained after every user interaction.

Try implementing this code to streamline your data entry tasks and improve efficiency.