Excel Calendars


Appointment Calendar
 Jay Walkenbach
 Erlandsen Data Consulting
Tools including:
 Create a large calendar (A4) for each month or a small calendar (pocket) for a whole year. The calendar automatically formats some holidays (included Easter Sunday etc.), you might need to customize the workbook to suit your needs. Updated 4.7.1999.
 Microsoft templates

Calendar Macro
Sub MakeCalendarPlanner()
'This variation leaves a space
'for putting in appointments
'Evelyn Woolston
Dim numcells As Range
Dim rownum As Integer
Dim colnum As Integer
Dim myyear, mymonth
Dim dayname As Range
Dim daynums As Range
Dim daysinmonth As Integer
Dim startdate As Date
myyear = InputBox("Enter the year" & Chr(10) & "for which you want aCalendar ")
mymonth = InputBox("Enter the month" & Chr(10) & "for which you want a Calendar ")
 Range("D1").Value = mymonth & myyear
 startdate = Range("D1").Value
 mycal = Range("D1").Value
 Set daynums = Range("B4:H10")
 'Format daynums
 daynums.NumberFormat = "d"

 Set dayname = Range("B3:H3")
 dayname(1).Value = "Mon"
 Range("B3").Select
    With Selection
        .AutoFill Destination:=Range("B3:H3"), Type:=xlFillDefault

    End With
    'Fills cells with days of week
 Range("D1:E1").MergeCells = True
'Put month and year into merged cell
'it automatically formats itself as a date ie the first of the month

'Find day of week on which Month starts
firstday = Weekday(mycal, 2)
'Put 1 under the correct day
Do While Month(mycal) = Month(startdate)
daynums(firstday).Value = mycal
firstday = firstday + 1
mycal = mycal + 1
Loop
'Format calendar
'Insert 1 row between each line of numbers
For insertingrows = 5 To 13 Step 2
  Rows(insertingrows).Select
    Selection.Insert Shift:=xlDown
    Next insertingrows
'Put a border around D1:E1
 Range("D1:E1").BorderAround LineStyle = xlBorderLineStyleContinuous
    Range("B3:H3").Select
    'Formats the day names as Blue with a white font
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .ColorIndex = 2
    End With
    With Selection.Interior
        .ColorIndex = 23
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    'Put a border around B3 to H15
    Range("B3:H15").BorderAround LineStyle = xlBorderLineStyleContinuous
    'Insert a border around blocks of 2 cells
    Set numcells = Range("B4:H15")
For colnum = 1 To 7
For rownum = 1 To 12 Step 2
numcells(rownum, colnum).Range("A1:A2").BorderAround LineStyle = xlBorderLineStyleContinuous
'A useful way of referring to a range of cells within a
'named range .  The A1:A2 means e.g. numcell(1,1) and 'numcell(2,1)
    Next rownum
    Next colnum
End Sub
Valid CSS! Valid XHTML 1.0!
 
Links verified 19 April, 2009