Highlight Pay Dates on Userform Calendar

[zoom38]

Good evening all,

Below is some code that I took from an article written by Lucas on this site on how to create a user form calendar. I have it running but I'd like to modify it so that on any month I choose, it automatically highlights pay dates. The pay dates are bi-weekly on Thursdays. I use 1/30/2014 as the base date and then calculate the rest of the dates using Mod 14. Lucas' code is a bit over my head and I cant' figure out how to incorporate the mod calculation into the calendar user form and highlight the pay dates.



Private Sub Build_Calendar()
'the routine that actually builds the calendar each time
If CreateCal = True Then
CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
'sets the focus for the todays date button
CommandButton1.SetFocus
For i = 1 To 42
If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) _
& "/1/" & (CB_Yr.Value))), ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
End If
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") = ((CB_Mth.Value)) Then
If Controls("D" & (i)).BackColor <> &H80000016 Then
Controls("D" & (i)).BackColor = &H80000018
Controls("D" & (i)).Font.Bold = True
End If
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
Else
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
Controls("D" & (i)).Font.Bold = False
End If
Next
End If
End Sub

Any help would be appreciated.
Thank you.

[arnelgp]

i added another sheet pay_date, with the date of all payroll.
you need to tweak to make it faster, maybe used array or collection (dicitonary) to
pre-load all the pay dates.

https://www.dropbox.com/scl/fi/ip9icc6epp8cxravmj6lw/click-cell-popup-calendar.xlsm?dl=0&rlkey=mpa3ux9bufflhr91hng8kmho4

[snb]

Somewhat simpler

[Paul_Hossler]

i added another sheet pay_date, with the date of all payroll.
you need to tweak to make it faster, maybe used array or collection (dicitonary) to
pre-load all the pay dates.

https://www.dropbox.com/scl/fi/ip9icc6epp8cxravmj6lw/click-cell-popup-calendar.xlsm?dl=0&rlkey=mpa3ux9bufflhr91hng8kmho4


It's really better to just attach the file directly to your post instead of using another file sharing service

My sig has instructions

[Paul_Hossler]

Somewhat simpler


Using Excel 365 I get an error

29490

[Paul_Hossler]

Good evening all,

Any help would be appreciated.
Thank you.

Can you attach a small sample workbook with the user form and code etc.

Makes it easier to see and test

[arnelgp]

It's really better to just attach the file directly to your post instead of using another file sharing service

My sig has instructions
i can't exceed size limit of 1 attachment.

[zoom38]

Thank you for the replies, unfortunately I am at work and don't have access to the file, it's on my home computer. I will work on it when I get home and advise.

Thank you again
Gary

[arnelgp]

Somewhat simpler
you just calculate it every 2 weeks?
you should start from 1/30/2014, adding 2 weeks thereafter to get
the correct paydate of thursday.

your paydate on march 2017 are 2, 16 and 30
while on my list it is 9 and 23.

[snb]

Nothing simpler than that: instead of y mod 14=4, you can change that minimal line of code to: y mod 14 =11.
No need to create a 'list of...'

[zoom38]

@Paul, I apologize, in my haste I just posted the code for the sub I needed modified. You're right, I'm sure it would've been easier for you all if I attached a workbook instead.

@ snb, thank you for the replies. Your code is way over my head and it threw an error that I couldn't figure out.

@arnelgp, thank you for your reply. It took me a bit to do, but I used your example and modified it using Mod so that I didn't have to create a new sheet for dates.
Attached is a small workbook with my code.

Thank you all for your help.
Gary
29492

[arnelgp]

very clever approach.
i made some changes (if you don't mind).
i change String date to Real dates (userform and your function).
also paydate start #1/30/2014#, so it will not highlight thursdays prior to that date.
also you do not need to call BA_Calculate_Difference_Between_Two_Dates() function
since i converted the string date to real date.

[snb]

@zoom

which error in which code line ?
Always remove or comment out 'option Explicit' before running my code.

[zoom38]

@arnelgp - no objection, thank you for making the code shorter and figuring out what I couldn't. I had an error when I declared them as dates (in my code). So when I declared them as strings, it was throwing an error until I used the Calculate Between Dates Function. Your code is much simpler.

@snb - I get a "Type mismatch" error on this line:
Me("L_5" & j).Caption = Application.WeekNum(CDate(SC_01.Tag) + 7 * (j - 1), 21) in

Private Sub SC_01_Change()
Your code is so far over my head I just gave up because of my lack of understanding. By the way, it errors in Excel 2007, I did not try it in any other version.

Thank you both again for taking the time to help me out.


Gary

[Exprings1987]

This macro allows you to highlight the pay date of any selected row in your userform calendar. This can be useful if you want to make sure that users are aware of their upcoming payment dates.