Range("A4").Offset(x * 2, 0).EntireRow.Insert ' Create Entry cells, format them centered, wrap text, and border ' Exit loop when calendar has correct number of days ' Stop when the last day of the month has been entered. ' Do only if current cell is not in Row 3 and is in Column 1.ĮlseIf cell.Row > 3 And cell.Column = 1 ThenĬell.Value = cell.Offset(-1, 6).Value + 1 ' Exit loop when calendar has correct number of If cell.Value > (FinalDay - StartDay) Then ' Stop when the last day of the month has been ' Do if current cell is not in 1st column.Ĭell.Value = cell.Offset(0, -1).Value + 1 ' Loop through range a3:g8 incrementing each cell after the "1" ' Place a "1" in cell position of the first day of the chosen
' Set variable and calculate the first day of the next month.įinalDay = DateSerial(CurYear, CurMonth + 1, 1) ' Set variables to identify the year and month as separate ' Set variable and get which day of the week the month starts. Range("a1").Value = Application.Text(MyInput, "mmmm yyyy") ' Put inputted month and year fully spelling out into "a1". ' Prepare a3:g7 for dates with left/top alignment, size, height ' Prepare a2:g2 for day of week labels with centering, size, HorizontalAlignment = xlCenterAcrossSelection ' Center the Month and Year label across a1:g1 with appropriate ' Prepare cell for Month and Year as fully spelled out. StartDay = DateValue(Month(StartDay) & "/1/" & _ ' - if so, reset StartDay to first day of month. ' Check if valid date but not the first of the month ' Get the date value of the beginning of inputted month. ' Allow user to end macro with Cancel in InputBox. MyInput = InputBox("Type in Month and year for Calendar ") ' Use InputBox to get desired month and year and set variable ' Clear area a1:g14 including any previous calendar. ' Prevent screen flashing while drawing calendar.
' Unprotect sheet if had previous calendar to prevent error.ĪctiveSheet.Protect DrawingObjects:=False, Contents:=False, _ Sample Visual Basic procedure Sub CalendarMaker() In Excel 2010, the option is available on the Customize Ribbon menu.
In Excel 2007, the option is available on the Popular menu. If the Developer ribbon is not visible, open Excel Options to enable it.