This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar highlights dates containing appointments or events, the clear button below the calendar deletes all filter conditions.
My previous post Excel calendar (vba) used a formula to extract events based on selected cell, this article shows you event code and a macro to filter the Excel defined Table itself.
How to use this workbook
The animated image below shows what happens with the Excel Table when you select different cells in the calendar, it also shows what happens when you click the "clear filter" cell.
The Excel defined table and calendar, unfortunately, have to be located like the image shows above. The Excel Table will filter out calendar rows if they are next to each other.
I would certainly have liked the calendar above the table but that makes it impossible to adjust the calendar's cell widths to the same size.
How I created this workbook
The calendar is dynamic meaning it changes automatically based on the selected year and month.
'Event code that is triggered when the user selects any cell in cell range E4:K9
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Check if selected cell is in cell range E4:K9 and the number of selected cells are 1
If Not Intersect(Target, Range("E4:K9")) Is Nothing And Target.Cells.Count = 1 Then
'Apply a filter to first column in Excel table named Table1 based on selected cell's date
Field:=1, Criteria1:=">=" & Target.Value, Operator:=xlAnd, Criteria2:="<" & Target.Value + 1
'Clear filter if cell G11 is selected
ElseIf Not Intersect(Target, Range("G11")) Is Nothing Then
'Run macro SortTable
'Sort table in an ascending order based on first column
.SortFields.Add Key:=Range("Table1[[#All],[Start]]"), SortOn:=xlSortOnValues, Order:= _
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Where to put the VBA code?
Right-click on the sheet name, it is located at the very bottom of the Excel screen.
Click "View code".
Paste event code to sheet module.
Return to excel.
Note, make sure you save the workbook with file extension *.xlsm in order attach the code to your workbook.