Forum Discussion

Roy Ravid's avatar
Roy Ravid
Copper Contributor
Jun 10, 2018

How to create a calandar

Hi,

I need some help with my excel program, I want to make a calendar that when you put the month and the year it will show it to you. My problem is that I don't know how to do it. Thanks!

 

 

Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 10, 2018

    Hi Roy,

    You may check any of calendar templates (File->New, search calendar), quite many of them show montly calendar based on selected year and month.

  • Man Fai Chan's avatar
    Man Fai Chan
    Iron Contributor
    Jun 10, 2018

    You may consider the following vba codes:

     

    Sub Gen_Calendar()
     Set Info = Sheets("Info")
     
     Set RS = Sheets.Add
     RS.Name = "Calendar " & Format(Now, "mmdd_hhnn")
     
     r_RS = 1
     
     RS.Cells(r_RS, 1) = "SUN"
     RS.Cells(r_RS, 2) = "MON"
     RS.Cells(r_RS, 3) = "TUE"
     RS.Cells(r_RS, 4) = "WED"
     RS.Cells(r_RS, 5) = "THU"
     RS.Cells(r_RS, 6) = "FRI"
     RS.Cells(r_RS, 7) = "SAT"

     d1 = Info.Range("B1")
     d2 = Info.Range("B2")
     
     With RS.Range("A:H")
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlCenter
     .Font.Name = "Times New Roman"
     .Font.Size = 8
     End With
     
     r_RS = r_RS + 1
     
     For i = d1 To d2
     Info.Range("B20") = i
     c_RS = Info.Range("B21") + 1
     
     If Info.Range("B21") = 0 Then r_RS = r_RS + 1
     
     RS.Cells(r_RS, c_RS) = i
     RS.Cells(r_RS, c_RS).NumberFormatLocal = "d-mmm"
     
     
     Next i
    End Sub

     You can try the download and import the code and then run the macro. 

     

    Hope this can help you. 

Resources