date selection in a cell?

**Lewis Koh** · 01-04-2010, 01:54 AM

Oh, sorry, I missed out the attached file.

The calendar is great. but when I double click on the yellow column, I can't choose the dates without pressing ESC or clicking on another cell. Is there a command line that controls this action?

May I know what does [A:A] means? Can I change to specific cells like M9:M17, M18:M22, etc?
I tried [M9:M17], [M18:M22], but [M9:M17],[M18:M22] are not working. Removing [M18:M22], [M9:M17] will work.

How do I put the calendar into my excel file?

**Ron Coderre** · 01-04-2010, 08:49 AM

Originally Posted by Lewis Koh

Oh, sorry, I missed out the attached file.

The calendar is great. but when I double click on the yellow column, I can't choose the dates without pressing ESC or clicking on another cell. Is there a command line that controls this action?

May I know what does [A:A] means? Can I change to specific cells like M9:M17, M18:M22, etc?
I tried [M9:M17], [M18:M22], but [M9:M17],[M18:M22] are not working. Removing [M18:M22], [M9:M17] will work.

How do I put the calendar into my excel file?

1) After dbl-clicking a Col_A cell, the Calendar form pops up.
•Click a date...it will be entered in the active cell and the form will close
or
•Click the Cancel button to just close the form

2)[A:A] designates that all Col_A cells, when dbl-clicked, engage the form.

3)To only use M9:M17 and M18:M22(...which is really M9:M22, right?)
Change the Sheet1 code to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(ActiveCell, [M9:M22]) Is Nothing Then
        PopUpCalEntry ActiveCell
    End If
End Sub

4)To use the pop-up calendar functionality in your workbook....
•Open the demo workbook AND your workbook
•[Alt]-[F11] to open the VBA editor
From the VBA Project window
•Drag the frmCal from the demo to your workbook
•Drag Module1 from the demo to your workbook
•Dbl-Click the demo Sheet1 to see the code (make sure it's edited for M9:M22)
•Copy all of the Sheet1 code
•Dbl-Click your sheet to see the code
•Paste the demo Sheet1 code into your sheet module
Done...your workbook should now display the pop-up when a cell in M9:M22 is dbl-clicked.

**Lewis Koh** · 01-04-2010, 08:40 PM

1) After dbl-clicking a Col_A cell, the Calendar form pops up.
•Click a date...it will be entered in the active cell and the form will close
or
•Click the Cancel button to just close the form

I can't choose the date after the pop-up. I had to click on another cell to sort of activate the calendar pop-up before I can choose a date.

2)[A:A] designates that all Col_A cells, when dbl-clicked, engage the form.
Does that mean if I wanted column M to be the input column, I should change to [M:M]?

3)To only use M9:M17 and M18:M22(...which is really M9:M22, right?)
Change the Sheet1 code to:

Oh sorry, I had other info on M18 so the column should be M9:M17 and M19:M22. I had a lot of other rows on column M that need not have date input, thus I'm wondering if I could set the pop-up on multiple rows with breaks in between them..

**Ron Coderre** · 01-05-2010, 09:07 AM

1) I don't understand why you are experiencing the odd calendar behavior.
I cannot replicate it on Excel 2003. When I dbl-click a target cell, the
calendar pops up. When I click on a date in the calendar, the date is posted
in the active cell and the form closes.

2) Yes, you would refer to Col_M by replaceing [A:A] with [M:M].

3) To refer to M9:M17, M19:M22...
•Select those two clusters of cells.
•Assign a name to them (I used MyDateCells)
•Use the below code in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(ActiveCell, [MyDateCells]) Is Nothing Then
        PopUpCalEntry ActiveCell
    End If
End Sub