date selection in a cell?

[Lewis Koh]

Hi,

Is it possible to click on a cell and a monthly calendar pops up for you to select a date to input? The output will be just the DD/MM/YY format.

Regards,
Lewis

[Ron Coderre]

I attached a working example of how to employ a pop-up calendar.
Of course, the file DOES have macros.

To see the demo...
Dbl-click a cell in Col_A
A pop-up calendar will display
Select and click a date to have it entered in the active cell
Click Cancel or Press ESC to close the calendar without entering a date.

Sheet1 has this VBA Code:

Option Explicit

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

The general module contains this code:

Option Explicit

Sub PopUpCalEntry(cCell As Range)
    Dim newDate As String
    Load frmCal

    newDate = Format(Date, "mm/dd/yyyy")

    frmCal.Calendar1.Value = newDate
    frmCal.Show
End Sub

The Calendar1 form has this VBA code:

Option Explicit

Private Sub Calendar1_Click()
    On Error GoTo errTrap
    With ActiveCell
        .Value = Calendar1.Value
        .NumberFormat = "mm/dd/yyyy"
    End With
errTrap:
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Is that something you can work with?

[Lewis Koh]

er....how do I put the codes in? I'm not familiar with Macros..I know where to open the editor but not sure where to copy to.

[teylyn]

take a few moments to study Ron's post. He says it quite clearly:

Sheet1 has this VBA Code:

and

The Calendar1 form has this VBA code:

and

The general module contains this code:

Then open the attached example file, hit ALT-F11 to open the VBE and click your way through the code in the Sheet1, Module1 and Form modules to see where the code is.

[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?

[Ron Coderre]

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]

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]

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

[teylyn]

Oh, could it be because I am using Excel 2000?

Could you please update your profile? The version information keeps experts from heading off in a direction that may not work in a specific version.

If you are asking for a solution for a version that differs from your regular version listed in the profile, please make sure to mention it.

[Lewis Koh]

Oh, I'm sorry, my office excel is 2000 but my home is 2003. I just realized it. I'll change my version since I am using it for work most of the time.

Does that mean 2000 and 2003 behave differently?

[tepraco]

Hello everyone and thanks in advance for any assistance.
The function works well, however, in my case after the double click to enable the calendar, I must first either
press ESC or change the active cell from the double click cell that initiated the calendar.

Then the function will work as expected? Any ideas how to resolve this annoyance? I am using Excel 2007. Perhaps some settings are not correct, but I cannot think what those settings may be.
Thanks again,
Tom

[teylyn]

tepraco,

please take a moment to read the forum rules and then start your own question. Link to this one if you feel it helps explain your issue.

cheers