Need a pop up calendar in a cell : Urgent for project

[jeetusaini85]

Dear All,

I m in the middle of an excel dashboard project which shows figures of current financial year by month and date from different sheets.

i have a drop down list of months from April'13 to March'14 in a cell (D2) and below this cell i put a specific date (in D3) to view the data figures of that particular month. i just need a macro that will pop up a calendar of that particular month which is mentioned by the drop down list, and when i click a date it will put in the cell.

In another way: When i click on D3 cell it should pop up a calendar of that month which was selected by drop down list.After that when i click on a date it should put in the cell (D3).

I m sorry if it doesn't make sense but i really need it.

Thank is advance.

 

[VoG]

This may help you Excel Tips: Put a Calendar Date Picker on an Excel Worksheet

 

[jeetusaini85]

Hi VoG,

Thanks for reply but i don't have "Microsoft Date and Time Picker Control 6.0 (SP6)" in my insert option under developer tool.

I am using MS 2010. Is this requirement fulfill by macro? If yes, can you provide the same?

Thanks.

 

[VoG]

Maybe try here - the 2003 version should work I Don't Have a Copy of mscal.ocx

 

[Sinbad]

I have had endless problems getting the calender to register on a 64 bit system. Just will not work, thus resorted to a vba version.

on the vba side for the spreadscheet I have this

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

     If Len(Target.Value) > 0 And Not IsDate(Target.Value) Then Exit Sub
     PopupCalendar_Cell Target.Address
     ActiveCell.Offset(0, 1).Activate 
     ActiveSheet.Calculate
End Sub

then i have a module containing this

Option Explicit
Public varStartDate As Variant

Type udDateType
    wYear As Integer
    wMonth As Integer
    wDay As Integer
End Type

Function PopupCalendar_Cell(sCell As String) As Variant

 varStartDate = Date
     frmKalender.Show vbModal
  sCell = "$b$5"
    Range(sCell) = Format(DateSerial(frmKalender!YYear, frmKalender!Month, frmKalender!tDay), "dd/mm/yyyy")
    Unload frmKalender

        
End Function

Function PopupCalendar_Form(ctl As Control) As Variant


    varStartDate = IIf(IsNull(ctl.Value), Date, ctl.Value)
    frmKalender.Show vbModal
    ctl.Text = Format(DateSerial(frmKalender!YYear, frmKalender!Month, frmKalender!tDay), "dd/mm/yyyy")
    Unload frmKalender
        
End Function

we have a form with a calader on it that is called, not sure how to show it to you here though... this form becomes the popup and is clickable thus illiminating the need for the calender control.

 

[jeetusaini85]

Hi Sinbad,

Thanks for sharing this code..... but i am unable to use this code as this occur error when i run it that - User-defined typed not defined on this line of code -

Function PopupCalendar_Form(ctl As Control) As Variant

Please help.....</pre>