can I create a drop down list to pick a date ?

[Retired Bill]

I have a column that I wish to have the date posted in. I would like to use
a DROP DOWN LIST with a calendar to let the user pick the date needed. I
would like the calendar to appear for the current MONTH/YEAR when the drop
down arrow is picked, with the option to move foreard or back month by month.

 

[Bob Bridges]

I don't do this much, but it seems to me if you want to use an ActiveX object
you have to do it with VBA programming. but someone here may be able to show
you another way.

 

[Gary''s Student]

Say we are using column B.

First put a calendar control on the worksheet:

Insert > Object... > Calendar Control (will have some number with it)

Next insert the following macros in teh worksheet code area:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If Target.Column = 2 Then
Cancel = True
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else
Cancel = False
Calendar1.Visible = False
End If
End Sub

Double click on column B to make the tool appear
Double click in the tool to pick a date
Double click off column B to make the tool vanish

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

and

http://groups.google.com/group/micr...aff113be3?lnk=gst&q=calendar#0eddab3aff113be3

 

[Retired Bill]

Thanks, I shall give it a try. Appreciate your time----------