set a default value in dropdoen list data validation

**aparunkumar** · 03-25-2017, 02:14 PM

I have a excel work book where in sheet1 cell a1 i create a data validation drop down list for an user to enter datas therein. I want one of the value in the drop down list as default value when a user opens the work book every time. some tips to succeed my aim.

**MarvinP** · 03-25-2017, 03:08 PM

Hi,
I think the code you want is:

Please Login or Register  to view this content.

But it must be behind the Workbook Module. See the attached. Change your name to one of the valid entries in A1.
Event Workbook Open set A1.xlsm

**aparunkumar** · 03-27-2017, 12:22 PM

Sir this is not the one i am looking for
I will explain. In cell A1 when a user click mouse a drop down list populates for eg .say numerics 0 to 10 serially displayed as drop down list. The user can select one of them in Cell A1. I want in cell A1 the first value of drop down list say 0 as a default value every time the user opens the work book so that initial value be 0 and the user can select his own choice from the dropdown list.

**JeteMc** · 03-28-2017, 11:02 AM

This proposed solution is based on a post made by JBeaucaire in this thread.
Before applying the data validation, you can enter a formula into the cell to show the default value. In this case the list of possible values is in E2:E12 so the formula in A1 is: =E2
When you open the workbook "0" will appear in A1, but you can use the DV drop box to change the value. When you do, the formula that was there is now gone for good, so in order for the user to get a "0" in cell A1 the next time s/he opens the file, the "Don't Save" option will have to be chosen when the file is closed.
Let us know if you have any questions.