Excel to notify date of birth
I want to create a worksheet with Staff dates of birth and get a notification
(column on worksheet blinking in any colour) 3 days to the staff's birthday date whenever i put on my system |
Excel to notify date of birth
"sinecodap" wrote: I want to create a worksheet with Staff dates of birth and get a notification (column on worksheet blinking in any colour) 3 days to the staff's birthday date whenever i put on my system Need a solution to this please |
Excel to notify date of birth
On May 16, 10:40*am, sinecodap .(donotspam)
wrote: I want to create a worksheet with Staff dates of birth and get a notification (column on worksheet blinking in any colour) 3 days to the staff's birthday date whenever i put on my system A blinking column would require the use of a timer and would be a load on your system. I would recommend going a different way. Maybe a message box saying the employees name or something like that. A simple For..Next statement could be used to loop through the employees and determine if the birthday is within 3 days prior of the current date. Assuming the employee names begin in A2 and go down and their associated birthday are in B2 and down, something like this would work. Of course, it would probably be better to just display one message box with all of the names listed. Sub Auto_Open() Dim i As Long For i = 2 To Cells(65536, 1).End(xlUp).Row If Date = (Cells(i, 2).Value - 3) And _ Date <= Cells(i, 2).Value Then MsgBox Cells(i, 1).Value End If Next i End Sub Or, you can highlight the person row instead of displaying a message box. Sub Auto_Open() Dim i As Long Cells.Interior.ColorIndex = xlNone For i = 2 To Cells(65536, 1).End(xlUp).Row If Date = (Cells(i, 2).Value - 3) And _ Date <= Cells(i, 2).Value Then Rows(i).EntireRow.Interior.ColorIndex = 40 End If Next i End Sub |
Excel to notify date of birth
I **hate** blinking text. Would you consider the alternative of simply
displaying a MessageBox showing you all the dates? Assuming the names are in Column A, the birthdays in Column B, the data starting on Row 2 and the worksheet name is Sheet3... Assuming the names are in Column A and the birthdates are in Column B and data starts on Row 2 and the data is on Sheet3... Private Sub Workbook_Open() Dim Dates As String Dim CellDate As Date Dim X As Long Dim DaysDiff As Long Dim LastRow As Long Const StartRow As Long = 2 With Worksheets("Sheet3") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow CellDate = .Cells(X, "B").Value DaysDiff = DateDiff("d", Date, DateSerial(Year(Now), _ Month(CellDate), Day(CellDate))) If 0 < DaysDiff And DaysDiff <= 3 Then Dates = Dates & .Cells(X, "A").Value & " - " & _ .Cells(X, "B").Value & vbCrLf End If Next If Len(Dates) 0 Then MsgBox Dates End With End Sub Rick "sinecodap" .(donotspam) wrote in message ... I want to create a worksheet with Staff dates of birth and get a notification (column on worksheet blinking in any colour) 3 days to the staff's birthday date whenever i put on my system |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com