Forum Discussion
Dryan91
Jan 04, 2024Copper Contributor
How to default a cell that is governed by Data Validation to a specific value
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.
Many thanks in advance!
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.