Solved

Popup calendar in Excel 2010

Posted on 2010-09-02
15
6,784 Views
Last Modified: 2012-05-10
I would like to create a pop-up calendar in certain cells of my worksheet in Excel 2010, to simplify the entry of dates. Please give me easy-to-follow step-by-step instructions. Thanks.
0
Comment
Question by:Anton Tajanlangit
  • 8
  • 5
15 Comments
 
LVL 5

Accepted Solution

by:
TimAllan earned 500 total points
ID: 33593175
Without reinventing the wheel, use this method :
http://www.fontstuff.com/vba/vbatut07.htm

The add-in and workbook zip files are at the bottom of the page.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33593373
Another wheel that does not require an add-in.

These instructions present a solution to display a date entry calendar control next to specific cells when those cells are selected - no menus or command buttons are required. The calendar control allows selection of the year, month, and the day of the month. Clicking in any other cell or double clicking on a day of the month closes the control. The calendar control is always positioned near the cell and so that it is entirely visible.

The Microsoft Calendar control is used to implement this solution. It is an ActiveX control used to input dates using a graphical month display. Using it to input dates is usually easier than typing the date directly into a cell. One of the most common techniques for using the calendar control on a worksheet is to add the control during development, make it invisible, and then show it when needed. If using a user form the control is placed on the form at design time. The problem with these techniques and with the calendar control in general is that there is a unique version installed with each version of Office and, if pre-loaded onto a worksheet or user form in one version of Office, it may not work correctly in another version of Office. The solution is to use late binding and load the control only when needed.

This article provides a VBA solution that works in all versions of Excel regardless of the version of the calendar control installed. The calendar is created only when needed and discarded (versus hidden) when not needed. The solution positions the control so that it is never hidden outside the visible range of cells, and frames the selected cell and calendar input control in blue. Because the calendar control was not installed with Excel prior to Excel 2007, instructions are included at the end of this article describing how to obtain the control.

The solution is provided in three parts: sample code that is placed in any worksheet SelectionChange event handlers to determine when to show the calendar input control, a general code module that contains the support code, and code to add to the ThisWorkook code module. Below is sample code illustrating how to use the solution. The sample shows the calendar input control when any cell in the range A1:D4 or F1:H4 is selected. Note that no calendar contol needs to be added to the worksheet, the code below adds and deletes the calendar control automatically.

[Begin Code Segment]

Private CalendarDisplayed As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, [A1:D4, F1:H4]) Is Nothing And Target.Address = Target(1).MergeArea.Address Then
        ShowCalendarInputControl Target
        CalendarDisplayed = True
    Else
        If CalendarDisplayed Then
            HideCalendarInputControl
            CalendarDisplayed = False
        End If
    End If
   
End Sub

Public Sub CalendarInputControl_DblClick()

    HideCalendarInputControl

End Sub

[End Code Segment]

Below is the support code that is placed in a new general code module. To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

[Begin Code Segment]

Option Explicit
   
Private Const CalendarInputControlName = "CalendarInputControl"
Private Const CalendarInputFrame1Name = "CalendarInputFrame1"
Private Const CalendarInputFrame2Name = "CalendarInputFrame2"

Public Sub HideCalendarInputControl()

    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Visible = False
    ActiveSheet.Shapes(CalendarInputFrame1Name).Delete
    ActiveSheet.Shapes(CalendarInputFrame2Name).Delete

End Sub

Public Sub ResetCalendarInputControl()

    Dim Calendar As Object
   
    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Delete
    Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Left:=0, Top:=0, Width:=180, Height:=120)
    If Err.Number <> 0 And Err.Number <> 1004 Then
       MsgBox "The Microsoft Calendar Control is not installed on this computer."
       HideCalendarInputControl
       Exit Sub
    End If
    Calendar.Name = CalendarInputControlName
    Calendar.Visible = False

End Sub

Public Sub ShowCalendarInputControl( _
      ByVal Cell As Range _
   )

    Dim Calendar As Object
    Dim CalendarFrame As Shape
    Dim CellFrame As Shape
    Dim HorizontalDelta As Double
    Dim VerticalDelta As Double
   
    HideCalendarInputControl
    If Cell.Left + Cell.Width + 5 + 182.5 > ActiveWindow.VisibleRange.Columns(ActiveWindow.VisibleRange.Columns.Count).Left Then
        HorizontalDelta = -Cell.Width - 10 - 182.5
    End If
    If Cell.Top + Cell.Height + 5 + 123 > ActiveWindow.VisibleRange.Rows(ActiveWindow.VisibleRange.Rows.Count).Top Then
        VerticalDelta = -Cell.Height - 10 - 123
    End If
    Set CellFrame = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Cell.Left, Cell.Top, Cell.Width + 1, Cell.Height + 1)
    CellFrame.Name = CalendarInputFrame1Name
    With CellFrame.OLEFormat.Object.ShapeRange
        .Fill.Visible = msoFalse
        .Line.ForeColor.SchemeColor = 12
        .Line.Weight = 2.5
    End With
    Set CalendarFrame = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Cell.Left + Cell.Width + 5 + HorizontalDelta, Cell.Top + Cell.Height + 5 + VerticalDelta, 182.5, 123)
    CalendarFrame.Name = CalendarInputFrame2Name
    With CalendarFrame.OLEFormat.Object.ShapeRange
        .Fill.Visible = msoFalse
        .Line.ForeColor.SchemeColor = 12
        .Line.Weight = 2.5
    End With
    On Error Resume Next
    Set Calendar = ActiveSheet.OLEObjects(CalendarInputControlName)
    On Error GoTo 0
    If Not Calendar Is Nothing Then
        With Calendar
            .Left = Cell.Left + Cell.Width + 7 + HorizontalDelta
            .Top = Cell.Top + Cell.Height + 7 + VerticalDelta
        End With
    End If
    Calendar.LinkedCell = Cell.Address
    Calendar.Visible = False
    Calendar.Visible = True

End Sub

[End Code Segment]

Below is the code that is placed in the ThisWorkbook code module.

[Begin Code Segment]

Private Sub Workbook_Open()

    ResetCalendarInputControl

End Sub

[End Code Segment]

Obtaining the Control

Visit this website to get versions of the control installed prior to Excel 2007:

   http://www.fontstuff.com/mailbag/qvba01.htm

Instructions are provided on how to install the control.

Additional Information

The names of the calendar control (as listed in the ActiveX control chooser) installed with the versions of Office since Office 97 are listed below.

   2007: Calendar Control 2007
   2003: Calendar Control 11.0
   2002: Calendar Control 10.0
   2000: Calendar Control 9.0
   97: Calendar Control 8.0

All versions are registered with the same class name, "MSCAL.Calendar.7", which is used to load the control as illustrated below.

   Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", ...)

The file name of the control has also remained consistant: "MSCAL.OCX".

A final note about the technique used. When any ActiveX control is added or deleted from a worksheet the VBA environment is reset which means all global variables, module level variables, and static variables are cleared. To get around this deficiency, the calendar control is deleted and added when the workbook is opened and then hidden and shown from that point forward. The reason it needs to be deleted and added is that the version of the control can change from workstation to workstation.

Kevin
0
 

Author Comment

by:Anton Tajanlangit
ID: 33595505
@TimAllan: I saw that website before I posted this here, but it seemed not to work. Is it good for Excel 2010?

@zorvek: That was a really detailed rundown! However I don't know how and where to use the code. I've never done this before. I use Excel 2010. Please take me through the steps.
0
 
LVL 5

Expert Comment

by:TimAllan
ID: 33595711
It should work for 2010, but I'm not sure if Microsoft have removed a feature that disables it.

Ok, try this : http://cpap.com.br/orlando/ExcelCalendarMore.asp?IdC=OrlMoreWin
Direct download of the file : http://cpap.com.br/orlando/includes/CDownloads.asp?Title=Calendar&File=Install_ExcelCalendar.exe
0
 

Author Comment

by:Anton Tajanlangit
ID: 33596010
I ran the downloaded file ExcelCalendar.exe but an error message came up:

"Could not load object because it is not available on this machine."

This message also pops up whenever I launch Excel. I'm running Excel 2010 on Windows 7 Ultimate.
0
 

Author Comment

by:Anton Tajanlangit
ID: 33596113
The calendar works from a right-click menu, but it's a quite complicated interface. Can I get something simpler, which won't require more user input than just the date?
0
 
LVL 5

Expert Comment

by:TimAllan
ID: 33596168
I will assume you have the 32 bit version of Windows 7
Ok, do a search for mscal.ocx or if you cant find it, download it from :
http://www.ocxdump.com/cgi-bin/testwrap/downloadcounts.cgi?rt=count&path=ocxfiles/M/MSCAL.OCX

Save it to c:\windows\system32
from the command prompt, run : regsvr32 c:\windows\system32\mscal.ocx
Press Enter.  Start Excel...
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Anton Tajanlangit
ID: 33596597
Done it from the command prompt, with another error popping up:

The module "c:\windows\system32\mscal.ocx" was loaded but the call to DllRegisterServer failed with error code 0x8002801c.

So Excel still behaves as before.
0
 
LVL 5

Expert Comment

by:TimAllan
ID: 33596684
You will need to turn UAC off in Windows 7 to run that command. Turn it off, reboot, then run it again.
0
 

Author Comment

by:Anton Tajanlangit
ID: 33596711
I found the solution. I just had to run cmd.exe as an administrator.

Now back to the pop-up calendar. I was asking about a simpler interface...
0
 

Author Comment

by:Anton Tajanlangit
ID: 33597379
@TimAllan: I tried the link in your first post, and am quite satisfied with the result. Will this work on any Windows machine I open the Excel file in? Or does the new machine have to be configured also, the way I did mine according to the VBA procedure?
0
 
LVL 5

Expert Comment

by:TimAllan
ID: 33597429
I think because Office 2010 now has mscal.ocx removed, you will have to configure all PCs with Office 2010 that way.  Shouldnt be too had with a decent batch file.

It will work on any Windows computer, except if you have Windows 7 64 bit, then you will need to copy the file to C:\Windows\SysWOW64
The command line will be regsvr32 c:\windows\sysWOW64\mscal.ocx

hope that helps...
0
 

Author Comment

by:Anton Tajanlangit
ID: 33597533
One last query: Is there a way that the calendar would pop up just next to the cell the date is to be entered in?
0
 

Author Closing Comment

by:Anton Tajanlangit
ID: 33749095
The link pointed to a very good step-by-step method.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now