build userform calendar using "cell counting" method looping named labels (SamT)
How would I adapt this cell counting method to work on a userform where I have named the day number labels as dnum1, dnum2 etc... to dnum42 it's a 6 x 7 calendar
Code:
Option Explicit
Private CalendarTable As Range
Private FirstCalendarCell As Long
Function DaysInMonth(dDate As Date) As Long
DaysInMonth = Day(EOM(dDate))
End Function
Function EOM(dDate As Date) As Date
dDate = Format(dDate, "d-m-yyyy") 'Adjust as needed per your System and Locale
EOM = DateAdd("d", -1, DateValue(Month(dDate) + 1 & "-1-" & Year(dDate)))
End Function
Function FirstWeekDayOfMonth(dDate As Date) As Long
FirstWeekDayOfMonth = Weekday(Month(dDate) & "-1-" & Year(dDate))
End Function
Sub Initialize_CalendarTable()
Const SinglesPrefix As String = " " 'One space less than cell with
Const DoublesPrefix As String = " " 'two spaces less than cell with
Dim c As Long
Dim D As Long
Set CalendarTable = Range("B3:H8") ' myCal_label_array? ufCal.dnum1 to ufCal.dnum42
FirstCalendarCell = FirstWeekDayOfMonth(Range("B1")) ' myDate = ufCal.uMonth.Caption & "-" & ufCal.uYear.Caption
Application.EnableEvents = False
D = FirstCalendarCell - 1
With CalendarTable ' myCal_label_array? - userform name is ufCal
.Cells.ClearContents ' loop all ufCal.dnum1 - ufCal.dnum42 and clear caption
For c = 1 To DaysInMonth(Range("B1")) ' myDate
.Cells(D + c).Value = c & vbLf ' dnum & D + c & .caption = c... or dnum(D +c).caption...
Next c
End With
Application.EnableEvents = True
End Sub
1 Attachment(s)
User form Calendar for bill scheduling
Attachment 14377
This has snb's code for initializing and SamT's for EOM calculating plus mine to insert bills on the user form.
Still working on the weekly sums, and choosing options to let user select what days to highlight for paydays. weekly. eow, bi-monthly.
Next step will be formatting to print.
Thank you again for your help.:bow: