Previous Topic Next Topic

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

Answer 1 / 2
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 ?
0
jake
May 13, 2010 at 07:49 AM
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.
0
Answer 2 / 2
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
0
Jake
May 14, 2010 at 01:02 PM
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.
0
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"
0
Jake
May 17, 2010 at 10:23 AM
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 ?
0
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
0