How to default a cell that is governed by Data Validation to a specific value

Copper Contributor


Hello all,


Firstly, I apologise if this has already been questioned and answered elsewhere within the community. 


I require a solution that would allow me to default a cell to a specific value. The range of cells in question are governed by Data Validation (in this case, a series of arbitrary values related to methods of manufacture). The range of cells have one thing in common that is "PLEASE SELECT". I require the sheet to default these range of cells to "PLEASE SELECT" every time the workbook is closed and re-opened (the purpose is to ensure that the user is prompted to select the correct value). I assume this will require a VBA and I am a complete novice when it comes to it.


Excel Query.PNG

Many thanks in advance!

1 Reply

Please test the following on a copy of the workbook.

Press Alt+F11 to activate the Visual Basic Editor.

In the Project Explorer pane on the left hand side, double-click ThisWorkbook under Microsoft Excel objects.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Range(Range("G2"), Range("F1").End(xlDown).Offset(0, 1)).Value = "PLEASE SELECT"
    Application.ScreenUpdating = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.