Date picker dropdown for a full column

Question

My goal is to make this spreadsheet easy to fill out quickly, and so the people using it have said a dropdown calendar-click date would be easier than manually entering for them. The calendar would need to go for each cell in column H starting at H4, and then minimize if it is not the active cell. Then H5 I would like to be able to enter in a new date, using the date picker. This is kind of advanced I think, can anyone help with this?

Answer 1

I Don't see a "calendar control", I do see Microsoft Date and Time Picker Control 6.0 (SP4) is that what you are talking about?

Hi,

If you look at the very bottom of this page you can download it. It's a ZIP file so extract it to a folder.

http://www.rondebruin.nl/win/s8/win003.htm

EDIT...My apologies the download include a PDF help document to tell you how to register it/

Look at this link for how to register the new control

https://support.office.com/en-za/article/Add-or-register-an-ActiveX-control-8fc743a1-e72b-4b55-af3a-85d326e53918?ui=en-US&rs=en-ZA&ad=ZA&fromAR=1

Answer 2

Hi,

On the developer tab click INSERT and click the small spanner icon on the bottom right of the ActiveX toolbox. Scroll down to 'Calendar Control' and click that.

Anywhere on the worksheet click and hold down the left mouse button and drag to get a calendar of the size you want.

Right click the worksheet tab | View code  and paste the code below in on the right. Back on the worksheet click 'Design Mode' on the developer tab to exit design mode. Click anywhere except column H to make the calendar disappear.

Now click in and column H cell from H4 down. Click a date on the calendar and it gets inserted into the active cell.

Private Sub Calendar1_Click()

    ActiveCell.Value = (Calendar1.Value)

    ActiveCell.NumberFormat = "dd mmm yy"

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("H4:H1000000")) Is Nothing Then

        Calendar1.Top = Target.Top + (Target.Height * 2)

        Calendar1.Left = Target.Left + Target.Width / 2 - Calendar1.Width / 2

        Calendar1.Visible = True

        Calendar1.Value = Now

    ElseIf Calendar1.Visible Then Calendar1.Visible = False

    End If

End Sub.

Answer 3

I'm on a work computer and can't really use these links, would you mind writing it out for me?

Answer 4

I Don't see a "calendar control", I do see Microsoft Date and Time Picker Control 6.0 (SP4) is that what you are talking about?

Answer 5

It's not as easy as it used to be, since the calendar control is no longer part of the Office 2010 installation.  The best workaround I've seen is to use a custom userform - here is an example file that will do what you want for cells H4:H25 using the selection change event and other code.

http://1drv.ms/1K8QySb