Excel Combo Box and calendar 1

[Rmcta]

I need help in creating an excel page with:
1) a combo box column
2) a colum with date where user must pick the date from a calendar. I wish to have the calendar be visible only when the cursor goes into the cell so that Users cannot type values. After they picked a value I wish the calendar to close.

After the user enters the data, the file will be e-mailed to me and I will import the data into an Access database. I hope this can be done.

I would great appreciate if anyone can guide me with the code.

Thank you!

 

[DaleWatson123321]

Rmcta,

One option is to use (from the menu): Data - Validation.

OPTION A - If your list of dates is relatively short, you could list the dates as ONE list. These would be the steps to follow:

1) On a separate sheet, create your list of dates.

2) Assign a range name to the list - e.g. "datelist".

3) In the column where you want the user to pick dates, in the first cell, choose Data - Validation from the menu.

4) Under the Settings tab, and under "Allow", choose "List".

5) Then under "Source", type: =datelist

Notice that you also have other options under the tabs: "Input Message" and "Error Alert".

OPTION B: - If your list of dates is LONGER, you might want to consider setting up 3 separate lists - one for MONTH, DAY, and YEAR.

Create the three separate lists on a separate sheet, and follow the same instructions as in Option A - except that you will use 3 different cells for MONTH, DAY, and YEAR.

For example:
- in cell A1, enter the label MONTH
- in cell B1, enter the label DAY
- in cell C1, enter the label YEAR
- in cell D1, enter the label DATE

- in cell A2, enter the Data Validation for the MONTH
- in cell B2, enter the Data Validation for the DAY
- in cell C2, enter the Data Validation for the YEAR
- in cell D2, enter the following formula: =DATE(C2,A2,B2)

Whether using Option A or B, you would copy the Data - Validation cells and formula down for each of the rows required.


OPTION C - This option might be the BEST option, as it includes a "visual" Calendar. Steps:

1) From the menu: Insert - Object, and select "Calendar Control 10.0

2) To re-size the Calendar or move its positon, first click on either the "Select Objects" icon (in the shape of an arrow), or click on the "Design Mode" icon (in the shape of a blue triangle - with pencil and ruler)

3) Then double-click on the Calendar.

4) Then add the middle line, as per below...

Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
End Sub

5) "X" out of the Visual Basic Editor window.

6) Click on the "Design Mode" icon - to "Exit"

7) Now the user can change the MONTH, DAY and YEAR of the visual Calendar.

8) And to enter a date in the date column, the user needs to:
a) Place the cursor on the cell where the date is to be entered, and
b) Click on the date on the Calendar.

Note: If the user has to enter a long list of dates, the Calendar will not stay in view. Therefore, an option would be to place the Calendar at the top of the screen, and "freeze the (horizontal) pane". To do this, place your cursor below the Calendar in Column A, and choose (from the menu) Window - Freeze Panes.

One final note: I'm using Excel 2002, and I'm not certain whether this Calendar option exists in earlier versions.

I hope these options help. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Rmcta]

Thank you DaleWatson123321 for your reply. I need users to pick dates all year long and I would really know if I can have the calendar appear only when they click on a cell and disappear as soon as they get out of it.

 

[Rmcta]

Hello again DaleWatson123321.
I have applied your Options C. All works great. I now only need guidance on how to make the calendar visible ONLY when users click on certain cells.

Thank you in advance to anyone who can guide me through that.