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