Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The calendar sheet allows you to enter a year and use a drop down list to select a month.
A small VBA event code tracks which cell you have selected and shows the corresponding events accordingly.
How this workbook works
The animated image above shows how to enter data and how to select a given date. Today's date is highlighted yellow, days with one or more events are also highlighted, in this example blue.
Excel extracts data dynamically meaning the named range grows automatically when new data is entered, we don't need to change the formula cell references.
How I created the calendar
Table of Contents
- Calendar
- Conditional formatting
- Formulas
- Visual basic for applications
- Download excel *.xlsm file
Drop down lists
Select year
- Select cell B2
- Go to tab "Data"
- Click "Data Validation" button
- Allow: List
- Source:2012,2013,2014,2015,2016
- Click OK
Select month
- Select cell E4
- Go to tab "Data"
- Click "Data Validation" button
- Allow: List
- Source:January, February, March, April, May, June, July, August, September, October, November, December
- Click OK
Headers
Type Year: in cell B2, Month: in cell E2 and so on...
Calculating dates (formula)
- Select cell B5
- Formula:
=DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1)-WEEKDAY(DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1),2)+1
- Select cell B6
- Formula:
=B5+1
- Copy cell B6 (Ctrl + c)
- Select cell range D5:H5
- Paste (Ctrl + v)
- Select cell B6
- Formula:
=B5+7
- Copy cell range C5:H5
- Select cell range C6:H6
- Paste (Ctrl + v)
- Copy cell range B6:H6 (Ctrl + c)
- Select cell range B7:H10
- Paste (Ctrl + v)
Conditional formatting
Highlight Today
- Select cell range B5:H10
- Go to tab "Home"
- Click "Conditional formatting" button
- Click "New Rule..."
- Click "Use a formula to determine which cells to format"
- Format values where this formula is true:
=B5=TODAY() - Click "Format..." button
- Go to "Fill" tab
- Pick a color
- Click OK
- Click OK
Change font color for dates not in selected month
- Repeat 1-5 steps above
- =MONTH(B5)<>MONTH($B$6)
- Click "Format..." button
- Go to "Font" tab
- Pick a color
- Click OK
- Click OK
Highlight days with events
- Repeat 1-5 steps above
- =OR(B5=INT(INDEX(Data,0,1)))
- Click "Format..." button
- Go to "Fill" tab
- Pick a color
- Click OK
- Click OK
Formulas
Dynamic named range
- Select sheet "Data"
- Go to tab "Formulas"
- Click "Name Manager" button
- Click "New..."
- Name: Data
- Refers to:
=OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3) - Click OK!
- Click Close
Event formulas
- Select sheet "Calendar"
- Select cell B13
- Array formula:
=IFERROR(INT(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(A1)), COLUMN(A1))), "")
- Enter this formula as an array formula, see these steps if you don't know how.
- Select cell D13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(B1)), COLUMN(A1)), "")
- Select cell F13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(C1)), COLUMN(B1)), "")
- Select cell H13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(D1)), COLUMN(C1)), "")
How to create an array formula
- Select a cell
- Click in formula bar
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter
Visual basic for applications
- Right click on Calendar sheet
- Click "View code"
- Copy vba code below.
'Event code that is executed every time a cell is selected in worksheet Calendar Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if selected cell address is in cell range B5:H10 If Not Intersect(Target, Range("B5:H10")) Is Nothing Then 'Save date to cell G2 Range("G2") = Target.Value End If End Sub - Paste to worksheet module.
Hide value in cell G2
- Select cell G2
- Press Ctrl + 1
- Go to tab "Number"
- Select category: Custom
- Type ;;;
- Click OK!
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Calendar with scheduling [vba]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]
Find the longest/smallest consecutive sequence of a value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
22 Responses to “Excel calendar [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
In the "Calculating dates (formula)" section, Step #2... you misspelled "January" as "Janauary" in the latter part of the formula.
Thanks!
[...] number formats, and much more. Home > Excel > Automate > Use a calendar to filter a table← Previous post - Use a calendar to filter a tableFiled in Automate, Drop down lists, Excel, Search/Lookup on Sep.10, [...]
[...] How to create an Excel calendar with VBA [Get Digital Help] [...]
hi,i am new to this blog.
but i hope i can get some help regarding a pop up calendar.
i found a pop up calendar for excel 2007,but when i try to use it in Excel 2010, the calendar does not appear. and i think it is related with the format behind.can you help me?thank you for any help and sorry for my poor English.
auni,
Do you have a link to the pop up calendar?
https://www.fontstuff.com/vba/vbatut07.htm
Hi, I would like to use this example with my data set however I'd like to visually show the amount of events per date to understand when are we the busiest, slowest, etc. and be able to forecast using this data. Ideally I would like some sort of data bar or color change indicating the level for each date (Jan 1 has 10 items while Jan 2 has 3 and I can visually see that in each cell instead of seeing numbers or a solid color for each cell (here yellow and blue).
Also, would it be possible to have Excel know the days of the month as in first 7 days of the month, last day of the month, every Monday of the month. I'd eventually like to get formulas set up to tell me on average how busy we are during these periods.
David,
Here is a basic example. It looks like a heat map.
Highlight dates VBA
Private Sub Worksheet_Change(ByVal Target As Range) Set Rng = Range("B5:H10") If Target.Address = "$C$2" Or Target.Address = "$E$2" Then 'Remove previous formatting With Rng.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With For Each Cell In Rng For Each Value In Worksheets("Data").Range("Data1") If Int(Value) = Cell Then a = a + 1 Next Value 'Set color If a > 0 Then With Cell.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent4 .TintAndShade = 1 - (a / 4) .PatternTintAndShade = 0 End With 'Reset counter a a = 0 End If Next Cell End IfDownload excel *.xlsm file
Calendar-David.xlsm
Contact me if you want more features.
[...] David asks: [...]
Hi there, I have downloaded the above calendar - and want to add another filter so I can categorize the events/meetings in the calendar, e.g. Reg for regulatory, Ben for benefits - and so when you click on these it will filter for that type - is this possible to do? Appreciate your help with this.
Thanks
Rosie,
Yes, it is possible. Upload a file and I´ll see what I can do.
This looks great. I would like to use your calendar for a spreadsheet I am currently using. How could I just change where the event section refers to so it will automatically fill in the dates and times I need.
To give you context, I have a spreadsheet with many columns containing information. In these columns I have multiple dates/times for some rows. I want to create a calendar that references my spreadsheet with its events. I would like to keep all the information so that I can look at it still in the detailed view of each day (with all the information from the original row and also be able to reference the spreadsheet for multiple times for the same row but on different days).
How would I change the where the events are referenced to?
How could I add another option for dates so that I can add an extra column of values (I have the original dates column in the data sheet linked to another workbook?
[…] I got this codes from: https://www.get-digital-help.com/2012/09/05/excel-calendar-vba/ […]
Dear Sir
Please I would like to know how to highlight 2nd and fourth Saturday and all sunday as weekend. Can you please suggest vba code or formula.
advance thanks.
Yours faithfully
buvanamali
[…] How to create an Excel calendar with VBA [Get Digital Help] […]
Hi,
Thank you so much for the helpful template. This is awesome! However, when I choose January, it gives me all N/A values, can you please let me know what's going on? Thanks
I love this template. I am trying to find a way to be able to show student schedules, and I think I could use this but I would have to alter it and I'm not completely sure what I am doing. I have students that come to me for a 3 week rotation. I want a monthly calendar that will highlight the time each student is with me and then also allows me to input schedules for each day. Example student one is with me for 7/3 to 7/23, then on 7/3 they have orientation, on 7/5 they have conference and department meeting. Etc.
Emily
Have you seen this calendar?
https://www.get-digital-help.com/2016/11/10/calendar-monthly-view/
I think it will suit your requirements better.
Hi, your calendar is great for me. I want to add a new column D in the Data sheet and show the details in the calendar sheet (I added a new column right after the "Text:", how can I revise the array formula to show it properly?
Hi, I would need a help on case where event lasts for more than one day. I've managed to fix conditional formating so that color is shown well in callendar however I'm stucked on event formulas. I'd like to show first events which are from previous day and then current day ones. Could you advice how to build these table formulas correctly?