Drop down that returns to the last selection and can be formated.

H

Husker87

I have a form that requires the user to enter a date. I have a years worth
of “Sunday’s†in a range. I used Date Validation but when you select a new
date the drop down returns to the top of the list. When I use a combo box
the drop down list returns to the last selection but won’t format “MMM-Dâ€.

Any suggestions on what I can use to get a date drop down list that returns
to the last selection. As the year goes on the users have to scroll down
quite a bit. (I only put 1 years works of weekly dates in the range)

Thanks
 
J

JLatham

Here's one way:
Put your list of Sundays on a sheet - can be any sheet in the workbook.
Format those cells the way you want the date to appear.
On the sheet where your users are to choose the date, use the Combo Box from
the Forms Toolbar, not the Toolbox. Right-click it and choose [Format
Control] and on the [Format] tab in the "Input Range" area, just go to where
you put the list of Sundays and highlight it, and then in the "Cell Link"
area choose or enter a cell address where the control can store the number
that is the list item number when one is chosen from the Combo Box. It could
be on the sheet with the list of Sundays or even hiding under the Combo Box
itself. Note that that cell must not be locked if the worksheet is to be
protected since it has to be able to change when new selection is made in the
Combo Box.

The Combo Box will always show the last choice made and it will show it in
the format that you've assigned to the items in the source list.
 
Top