Always Show drop down list Of Data Validation List

[ifeelalone]

Hey everyone!

Is there a way to always show drop the down list Of data validation list?

 

[Yongle]

Hi @ifeelalone - only possible with combobox NOT cell dropdown

But there is a trick that uses a combobox placed above the cell......

The trick
- add an Active-X combobox (do not need to do anything to it)
- add a named range called MyList for the list of dropdown values and put values in that range of cells
- add the code below in the sheet module (right click on sheet tab \ View Code \ paste code in the code window \ {ALT}{F11} to go back to Excel )
- when you click on any cell the code runs and the combobox is resized and moved to sit over cell B2 and the dropdown list appears

Sub DropdownTrick()
    Dim cel As Range:       Set cel = Range("B2")
    With Me.ComboBox1
        .ListFillRange = ThisWorkbook.Names("MyList").RefersTo
        .DropDown
        .LinkedCell = cel.Address
        .Left = cel.Left
        .Top = cel.Top
        .Width = cel.Width
        .Height = cel.Height
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call DropdownTrick
End Sub