Lesson#204: Make any calendar in excel with VBA

If you need to create a monthly calendar in Excel, VBA can be a powerful tool to automate the process. With a few lines of code, you can prompt the user to enter a month and year, generate a 7×6 table of dates, and format the calendar with proper headers and column widths. In this blog post, we’ll walk through the steps to create a custom VBA program that will create a monthly calendar in Excel with just a single button click.

1. Go to the Developer Tab in Excel. If you haven’t activated the Developer Tab then see here how to do it.
2. Insert a Command Button by clicking on ‘Insert’.
3. Create a button on the sheet and double-click to enter the code.

Private Sub CommandButton1_Click()
    Dim MonthName As String
    Dim YearNum As Integer
    Dim StartDate As Date
    Dim EndDate As Date
    Dim i As Integer
    Dim j As Integer
    Dim RowNum As Integer
    Dim ColNum As Integer
    
    ' Get user input for month and year
    MonthName = InputBox("Enter the name of the month (e.g. January):")
    YearNum = InputBox("Enter the year:")
    
    ' Calculate the start and end dates for the month
    StartDate = DateSerial(YearNum, Month(MonthName), 1)
    EndDate = DateSerial(YearNum, Month(MonthName) + 1, 1) - 1
    
    ' Set up the calendar sheet with headers
    Dim calendarSheet As Worksheet
    Set calendarSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    calendarSheet.Name = MonthName & "-" & YearNum ' Rename the sheet to "month-year"
    calendarSheet.Range("A1:G1") = Split("Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday", ",")
    calendarSheet.Range("A1:G1").Font.Bold = True
    
    ' Add the month and year header
    calendarSheet.Range("A1:G1").Merge
    calendarSheet.Range("A1") = MonthName & "-" & YearNum ' Show header as "January-2015" format
    calendarSheet.Range("A1").Font.Bold = True
    calendarSheet.Range("A1").HorizontalAlignment = xlCenter
    
    ' Initialize row and column numbers
    RowNum = 2
    ColNum = Weekday(StartDate)
    
    ' Fill in the calendar with dates
    For i = 1 To EndDate - StartDate + 1
        calendarSheet.Cells(RowNum, ColNum) = StartDate + i - 1
        ColNum = ColNum + 1
        If ColNum > 7 Then
            ColNum = 1
            RowNum = RowNum + 1
        End If
    Next i
    
    ' Format the calendar cells
    calendarSheet.Range("A1:G7").HorizontalAlignment = xlCenter
    calendarSheet.Range("A2:G7").NumberFormat = "dd"
    calendarSheet.Range("A2:G7").VerticalAlignment = xlCenter
    calendarSheet.Range("A2:G7").WrapText = True
    calendarSheet.Range("A1:G1").ColumnWidth = 10
    calendarSheet.Range("A2:G7").RowHeight = 50
End Sub

Creating a monthly calendar in Excel can be a time-consuming task, but with VBA, you can automate the process and save yourself valuable time. By using the code we’ve developed in this blog post, you can easily create monthly calendars for any year and month, with proper headers and formatting, in just a few seconds. Whether you’re a student, a professional, or just someone who needs to keep track of your schedule, this VBA program can be a useful tool for you. Give it a try, and see how easy it can be to create a monthly calendar in Excel!