How to Create a Calendar Using Excel

Microsoft Excel is an optimum environment to create and maintain a calendar. Using the different cells, you can easily move from day to day to update the calendar or add in new events and appointments. It is not hard to create a calendar in Excel. Microsoft has supplied a simple CalendarMaker macro that inserts into the Visual Basic editor in Excel that will create the calendar for you. All you will need to do is copy the code to have your monthly calendar in Excel.

...
Use Excel to create a calendar.

Step

Open Microsoft Excel. Click on the "Tools" menu, point to "Macro" and click on "Visual Basic Editor" if using Excel 2003 or earlier. For Excel 2007/2010, click on the "Developer" tab and then click on "Visual Basic." If you don't see the "Developer" tab, click on the "Office" button and then "Excel Options." On the first screen, click on the check box next to "Show Developer tab on the Ribbon" and then click "OK." The "Developer" tab should show now.

Step

Click on the "Insert" menu item in the Visual Basic Editor. Click on "Module" to start a new code module.

Step

Copy the first part of the following code in the new module:

Step

" Sub CalendarMaker()

Step

' Put inputted month and year fully spelling out into "a1". Range("a1").Value = Application.Text(MyInput, "mmmm yyyy") ' Set variable and get which day of the week the month starts. DayofWeek = WeekDay(StartDay) ' Set variables to identify the year and month as separate ' variables. CurYear = Year(StartDay) CurMonth = Month(StartDay) ' Set variable and calculate the first day of the next month. FinalDay = DateSerial(CurYear, CurMonth + 1, 1) ' Place a "1" in cell position of the first day of the chosen ' month based on DayofWeek."

Step

Remove the quotation marks on the first and last line of the code.

Step

Copy the rest of the code that takes the format from the code above and create the actual calendar. Paste the code right under where the code from above ends.

Step

' Error causes msgbox to indicate the problem, provides new input box, ' and resumes at the line that caused the error. MyErrorTrap: MsgBox "You may not have entered your Month and Year correctly." & Chr(13) & "Spell the Month correctly" & " (or use 3 letter abbreviation)" _ & Chr(13) & "and 4 digits for the Year" MyInput = InputBox("Type in Month and year for Calendar") If MyInput = "" Then Exit Sub Resume End Sub"

Step

Remove the quotation marks on the first and last line of the code. Click on the "Save" icon on the toolbar. Click on the "File" menu item and then "Close and Return to Microsoft Excel."

Step

Run the "CalendarMaker" module. In Excel 2007/2010, click on the "Developer" tab and then click on "Macros." In earlier versions of Excel, click on "Tools," point to "Macros" and then click on "Macros." Select the "CalendarMaker" macro from the list in the window that pops up and then click on "Run."