VBA Birthday Reminder

Hi,

I have a list with dates of my team member's Birthdays and I am trying to find a way to get a pop-up Birthday Reminder message from VBA when the user open excel file.

Col A Col B Col C Col D

Name Dates Day Status

ABC 03/20/2019 Wed 1

DEF 03/23/2019 Sat

GHI 09/21/2019 Sun

JKL 11/04/2019 Mon

I am using the below code, which is working partially fine. Only problem with the code is that if birthday falls on weekends (Saturday or Sunday) then user will not get any pop-up message of the Birthday that happened on last weekend when user open excel file on Monday. So what I am trying to accomplish is that user should get a pop-up on next Monday if there were any Birthdays on previous weekend.

In the above data, two users have their Birthday on Sat, 03/23/2019 and Sun, 09/21/2019 and the below code will not show a pop-up on Monday. I want a code which show a pop-up on Mon, 03/25/2019 of Sat, 03/23/2019 Birthday.

Please Login or Register  to view this content.

I really appreciate if somebody help me with a new VBA code or the above can be modified to my requirement will help me a lot.

Thanks in advance!

Pradeep Mallur

Hi Pradeep,

If you dont mind i would like a file for my team too, will appreciate if you take personal data out and I can use this for my team too.

I can see Phuocam replied with the amended code for weekends birthday, hope it works.

Thanks in advance for support

Try

PHP Code: 

Sub Birthday_Reminder()
Dim ans As String
Dim cell As Range
Dim tempDate As String
tempDate = Format(Date, "ddmm")
    For Each cell In Sheets("Sheet1").Range("B2:B10")
        If Format(cell.Value, "ddmm") = tempDate Then
            MsgBox "Hey! " & vbNewLine & vbNewLine & _
            "Team Birthdays for Today: " & vbNewLine & ans & ".", vbInformation, "Team Birthday Reminder"
        ElseIf Format(Application.WorkDay(cell - 1, 1), "ddmm") = tempDate Then
            MsgBox "Hey! " & vbNewLine & vbNewLine & _
            "Team Birthdays on Previous Weekend: " & vbNewLine & ans & ".", vbInformation, "Team Birthday Reminder"
        End If
    Next
   
End Sub 


Hi,

The above code is not working. I am just getting the message pop-up without name.

Thank you so much Phuocam for your time. I will use the first code, which you have provided.