How to show default values with drop down list?

Question

Is it possible to show default values in a cell where it also has INDIRECT drop down list?

Illustration is in following link: http://imgur.com/a/h0pZV

As you can see in the picture, the drop down shows country using Data Validation List from a list of country.

Is it possible this cell(A11) always display default values as "Please select" in that cell when there is no values from the drop down list?

But when the selection eg. USA, is removed(DEL), It will display "Please select" again instead of blanks.

Using formula is preferable than vba. Any inputs are appreciated.

Thanks.

I am using the same thing, you have two choices, either enter Select One: as one of the options of your drop down lists (I don't like this) or have a vba code to write (overrides) "Select One:" in the cells. If the user chooses one, then you are fine, if the user deletes it will be gone and to make Select One: appear again, you need to have a worksheet Change event to write that back. The easiest way is to write a validation macro to check the validity of the entries and also to make sure that required fields have been selected and not left blank. — Ibo

Tragamor Tragamor · Accepted Answer · 2017-03-02T17:16:15

As stated, you need to use a worksheet change event to override the value in the cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    For Each Cell In UsedRange.SpecialCells(xlCellTypeAllValidation)
        If Cell.Value = "" Then Cell.Value = "Please Select:"
    Next Cell
End Sub

How to show default values with drop down list?

1 Answers