Forum Discussion

Dryan91's avatar
Dryan91
Copper Contributor
Jan 04, 2024

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.

Resources