How to Create a Calendar in Your Excel Worksheet with VBA Script

Excel has a wide range of usage. In this article, we will show you how to create a calendar in a worksheet by using a macro.

Except for storing and analyzing data in your Excel, you can also use it to finish other tasks. For example, you can collect information by using Excel, or you can note down your planning in the worksheet. Today we have found a new usage. You are able to create a calendar in a worksheet. You can see the effect in the image below.

You can input some tasks into this calendar. And the function is the same as a memorandum. By using the Excel, those tasks will be clearer. Now follow the steps in the following part and see how it works.

Create a Calendar

1. Press the button “Alt +F11” on the keyboard to open the worksheet.
2. And then insert a new module in the Visual Basic editor.
3. Now copying the following codes into the new module:

Sub Create_Monthly_Calender()
    Dim firstweekday As Integer, EndDay As Integer, _
    FirstWeekColumnIndex As Integer, AssignmentDate As Integer, _
    FirstCountNumber As Integer, SecondCountNumber As Integer, _
    LastDay As Range, objRange As Range, RowIndexofLastday As Integer, FirstCountforTargetRange As Integer, SecondCountforTargetRange As Integer

    firstday = InputBox("Input the year, month and the first day with this format: year/month/day")
    If firstday = "" Then Exit Sub


    Range("A1:G1").Merge
    Range("A1") = Year(firstday) & "." & Month(firstday)
    Range("A2") = "Sunday"
    Range("A2").AutoFill Destination:=Range("A2:G2"), Type:=xlFillDefault
    firstweekday = Application.WorksheetFunction.Weekday(firstday)
    Cells(3, firstweekday) = 1

    Select Case Month(firstday)
        Case 1, 3, 5, 7, 8, 10, 12
            EndDay = 31
        Case 4, 6, 9, 11
            EndDay = 30
        Case 2
            If (Year(firstday) Mod 4) = 0 And (Year(firstday) Mod 100) <> 0 Or ((Year(firstday) Mod 400) = 0) Then
                EndDay = 29
            Else
                EndDay = 28
            End If
    End Select

    For FirstWeekColumnIndex = 1 To (7 - firstweekday)
        Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex) = Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex - 1) + 1
    Next FirstWeekColumnIndex

    AssignmentDate = Range("G3") + 1
    For FirstCountNumber = 2 To 10 Step 2
        For SecondCountNumber = 0 To 6
            Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = AssignmentDate
            AssignmentDate = AssignmentDate + 1
            If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then
                Exit For
            End If
        Next SecondCountNumber
        If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then
            Exit For
        End If
    Next FirstCountNumber

    ’set format for the range
    With Range("A1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Size = 16
        .Font.Bold = True
        .Interior.Color = RGB(196, 202, 201)
    End With

    With Range("A2:G2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
    End With

    For Each LastDay In ActiveSheet.UsedRange
        If LastDay = EndDay Then
            RowIndexofLastday = LastDay.Row
        End If
    Next

    For FirstCountforTargetRange = RowIndexofLastday To 3 Step -2
            With Range("A" & FirstCountforTargetRange, "G" & FirstCountforTargetRange)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .RowHeight = 20
            End With
    Next FirstCountforTargetRange

        For SecondCountforTargetRange = RowIndexofLastday + 1 To 4 Step -2
            With Range("A" & SecondCountforTargetRange, "G" & SecondCountforTargetRange)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Font.Bold = True
                .RowHeight = 50
                .ColumnWidth = 12
            End With
    Next SecondCountforTargetRange

    Set objRange = Range("A1", "G" & (RowIndexofLastday + 1))
    With objRange.Borders
        .Color = vbBlack
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With

    ActiveWindow.DisplayGridlines = False
    Cells(3, firstweekday).Offset(1, 0).Select
End Sub

Thus is a very lone macro. But you can directly use it. And you don’t need to modify it.

4. Then press the button “F5” on the keyboard.
5. In the pop up window, input the date of the first day with the format “Year/Month/Day”. The “Day” can be any day in this month, here we will input “1”.
6. And then click the button “OK”. Next you will come back to the editor. In order to check the result, you can come back to the worksheet.

The calendar has already appeared in the worksheet. And the cursor will be positioned at the blank cell of the first day.

On the other hand, if you are not satisfied with the cells format, you can also modify the codes according to your preference. With this calendar, you are sure to improve your work efficiency.

Excel is not Less Prone to Failure

If you use Excel frequently, it will not be hard for you to find that Excel will always corrupt. Most of the time, exit Excel and then restart it can solve the problem. However, sometimes you will meet with severe Excel corruption. And at this moment, you can use our powerful tool to repair Excel xlsx data error. By using this tool, almost all the errors can be solved easily.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx file and outlook repair software products. For more information visit www.datanumen.com