+ Reply to Thread
Results 1 to 6 of 6

VBA Birthday Reminder

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Question 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

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,024

    Re: VBA Birthday Reminder

    Try
    PHP Code: 
    Sub Birthday_Reminder()
    Dim ans As String
    Dim cell 
    As Range
    Dim temp 
    As Long
    For Each cell In Sheets("Sheet1").Range("A2:A30")
    temp 0
        
    If Weekday(cellvbMonday) > 5 Then temp Weekday(cellvbMonday)
            If 
    Month(cell temp) = Month(Date) And Day(cell temp) = Day(Date_
            
    And cell.Offset(02) = "" Then
                ans 
    ans vbNewLine Sheets("Sheet1").Cells(cell.Row26).Value
                cell
    .Offset(02) = 1
                MsgBox 
    "Hey! " vbNewLine vbNewLine _
                
    "Team Birthdays for Today: " vbNewLine ans "."vbInformation"Team Birthday Reminder"
            
    End If
        
    Next
    End Sub 
    Last edited by Phuocam; 03-21-2019 at 06:30 AM.

  3. #3
    Registered User
    Join Date
    05-27-2014
    Location
    Manchester
    MS-Off Ver
    2007, 2010
    Posts
    73

    Re: VBA Birthday Reminder

    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

  4. #4
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: VBA Birthday Reminder

    Thank You Phuocam! The code works great.

    Is it possible to make two message Boxes: One Msgbox for Birthdays that fall on weekday and other Msgbox for Birthdays that fall on Weekends.

    I tried to put one more if condition after For Next, but i am not getting the desired result.

    Please Login or Register  to view this content.
    Pleaes help with his.
    Last edited by pradeepdeepu_001; 03-24-2019 at 11:41 PM.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,024

    Re: VBA Birthday Reminder

    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 11), "ddmm") = tempDate Then
                MsgBox 
    "Hey! " vbNewLine vbNewLine _
                
    "Team Birthdays on Previous Weekend: " vbNewLine ans "."vbInformation"Team Birthday Reminder"
            
    End If
        
    Next
       
    End Sub 

  6. #6
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: VBA Birthday Reminder

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. E-mail; Birthday reminder, Week difference
    By Aagaard806 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2018, 09:06 AM
  2. [SOLVED] Automatic Email or Reminder When Birthday is Approaching
    By jebindavidson in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-08-2016, 08:59 AM
  3. [SOLVED] Birthday reminder formula problem
    By Telegraph Sam in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-01-2016, 10:36 AM
  4. Macro to send birthday reminder via outlook email
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-01-2014, 10:29 AM
  5. Birthday and work anniversary reminder from excel file to outlook email
    By paulbochniak in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2014, 09:21 PM
  6. Birthday reminder
    By Desciplejustin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-16-2013, 11:48 AM
  7. wish to make a Birthday reminder!!
    By kiran1810 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-08-2011, 03:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1