You need to add a calendar in Excel with a popup that appears the moment you click on a certain cell? Do you want to give a more beautiful effect to your excel sheet and prevent the user from entering the date by typing it? Well read on, in this article I will explain to you how to add a calendar in excel in a few simple steps.
All you need to do to have an Excel calendar is arm yourself with patience and follow what I will show you in the next lines.
If you don't have calendar control you need to download the file MSCAL.OCX (on this page) and after pasting it in the folder c: windowssystem32 register it with regsvr32 mscal.ocx typing it in the window Run (WIN + R). When finished you can upload it to Tools> References> Browse.
How to create a calendar with Excel
add a calendar in excel you must first activate in References Excel, in the environment VBA, Microsoft Calendar Control 8.0 (if you have the 2010 version of Excel). To do it you just need to go up Development and click on the Visual Basic icon (top left) to open the environment Microsoft Visual Basic Application.
If you do not find yourself in the top menu the item Development, to activate it you have to go up File> Options> Customize Ribbon. In the right group choose the item Main cards, tick the item Development and press the button OK.
In the environment VBA click, on the left, on the sheet you are working on and copy and paste the following lines of code:
Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ' ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select Calendar1.Visible = False End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("I4:I8"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar 'Calendar1.Value = Date If Not IsDate(Target.Value) Then Calendar1.Value = Date Else Calendar1.Value = Target.Value End If ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub
To bring up the calendar object go to Development> Insert> Other Controls (Icon with hammer and wrench) and here in the window that appears choose Calendar Control 8.0, placing the cursor between cells I4: I8.
Save everything and try to click on a cell between I4 and I8. You can of course change the range by editing it in this line of code If Not Application.Intersect(Range(“I4:I8”), Target) Is Nothing Then
Setting Calendar1.visible = False in the function Private Sub Calendar1_Click(), you can make sure that when you click on the calendar popup this will disappear.
How to compare two tables in Excel
