Creating a drop-down list in Excel

1
votes

I'm changing an old Excel spreadsheet and have got a user-request to add a drop-down list to a date selection field.

Data Validation is already turned on for the cell in question with a "<=TODAY()" clause (the sheet contains information on production efficiency, no idea going forward since data is not there).

The user requests a drop-down listing the past seven days. I can make a list and enumerate it in VBA, but I am having difficulties coming up with a way to apply a drop-down list to a cell without using Validation.

Is this possible?

2
excelexcel-2010vba

2 Answers

4
votes

Here are some ways you can try:

  1. Add a datepicker to the cell (and keep the formula validation you put)
    a. See some standard way on MSDN
    b. or with an addin on this blog
  2. Change dynamicaly your validation to a dropdown list when the Workbook is opened or the worksheet activated with an event procedure and add another event procedure OnChange to check if the value entered is before today.
1
votes

Why not replace the existing data validation rule with a list rule? Just create a new sheet and enter in A1:A7

=TODAY()
=A1-1
...
=A1-6

Then set your data validation rule to "List" and set the Source to A1:A7 on the new sheet. Then hide the sheet just to keep it out of the way. This should preserve the old validation requirement.