# Calendar Control 10.

Solved/Closed
Jake - May 12, 2010 at 11:25 AM
Jake - May 21, 2010 at 09:08 AM
https://authentification.site/files/22400897/Book1.xls

Please view the attached link to see what i would like done.

Thanks
Related:

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 12, 2010 at 12:53 PM
So jake, you want to click on the date on the calendar control and know whose bday it is ?
Yes, is that even possible. I'm pretty new at excel. I work for a company that has 400 + employees, i want to be able to link special dates like birthdays, anniversaries etc.
The result being when I click the date on the calendar the info that is linked to that date is displayed. Thanks for your help.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 13, 2010 at 11:03 AM
See if this help

1. Press ALT + F11 to enter VBE
2. Press CTRL + R to open project explorer
3. Double click on the sheet where the calendar control and dates are
4. Paste this code

```Private Sub Calendar1_Click()
Dim Dt As String
Dim lMaxRows As Long
Dim vTemp As Variant
Dim sBdayFor As String

Dt = Format(Calendar1, "MMDD")

lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row

sBdayFor = ""
Do While Cells(lMaxRows, "B") <> ""

vTemp = Cells(lMaxRows, "A")

If ((IsDate(vTemp)) And (vTemp <> "")) Then

vTemp = Format(CDate(vTemp), "MMDD")

If (vTemp = Dt) Then

If (sBdayFor <> "") Then sBdayFor = vbCrLf & sBdayFor

sBdayFor = Cells(lMaxRows, "B") & sBdayFor

End If
End If

lMaxRows = lMaxRows - 1
Loop

If (sBdayFor <> "") Then
MsgBox (Calendar1 & " is Birthday For following:" & vbCrLf & sBdayFor)
End If

End Sub```
Great.
One problem though, suppose 2 or more Birthdays fall on the same date. This macro only returns the first name. It does not return the second or third name. Can you adjust ? Also Is there a way to format the dates that are linked to the calendar. I would like them to be in a different colour, that way it draws attention to the users.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 05:51 PM
There was a typo in code., I corrected the code
If (sBdayFor <> "") Then sBdayFor = vbCrLf & sBdayFor
is the line that was corrected

This take care of having a list of all the folks whose bday fall on the date being clicked.

I am not sure what you meant by "format the dates"
Thank you.
eg. my birthday is Feb 4, I want that date to be highlighted in a different colour, so that it stands out to the user. I want that for all the dates.
How do I protect Visual Basic so that no one messes with the code ?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2010 at 10:29 AM
if you are showing names and DOB via msgbox as was in the code that I gave you earlier, then it is not possible.If you are showing date some other way, then I am still not getting you how the dates are. Are you saying that you will show all the dates and names and only highlight those for which today is the DOB ?

To protect, in VBE environment, go to tool, and choose vba properties, There you will option for protection