I have two columns, one called NAME and the other BIRTHDAY. I want a MsgBox
that appear when a cell in BIRTHDAY take place during the current month, and
display the name of the person.Something like "JASON, PETER and JOHN HAVE
BIRTHDAY THIS MONTH" . Moreover, I want the MsgBox to pup up as soon as I
open the workbook.

Could somebody help me with the code?

Put the below code in the Workbook_Open() event of your workbook.

Also Please...
Change"Book1.xls" to whatever your workbook name is.
Change "Sheet1" to whatever you worksheet name is.
Change "B2:B8" to whatever your range is that has the birthday dates in them.
The below code assumes that the Birthday Names are 1 column to the left of
the birthday dates (Ex. names in column A and dates in column B).

The below code will execute everytime the workbook is opened. If you want
this to work everytime the workbook is "changed" as well you will have to add
this code to the Worksheet_Change event and add a few more lines.

Private Sub Workbook_Open()

Dim BirthdayList() As String, BirthdayNameList As String
Dim Cell As Range
Dim Counter As Integer, Counter2 As Integer

ReDim BirthdayList(0)
For Each Cell In
Workbooks("Book1.xls").Worksheets("Sheet1").Range( "B2:B8")
If IsDate(Cell) Then
If Month(Now) = Month(Cell) Then
BirthdayList(Counter) = Cell.Offset(0, -1).Value
Counter = Counter + 1
ReDim Preserve BirthdayList(UBound(BirthdayList) + 1)
End If
End If
Next Cell

If UBound(BirthdayList) 0 Then
ReDim Preserve BirthdayList(UBound(BirthdayList) - 1)
End If

If Counter = 0 Then
BirthdayNameList = "Nobody Has A Birthday This Month!"
MsgBox BirthdayNameList
ElseIf Counter = 1 Then
BirthdayNameList = BirthdayList(0) & " Has A Birthday This Month!"
MsgBox BirthdayNameList
ElseIf Counter = 2 Then
BirthdayNameList = BirthdayList(0) & " And " & BirthdayList(1) & _
" Have Birthday's This Month!"
MsgBox BirthdayNameList
Else
BirthdayNameList = BirthdayList(0)
For Counter2 = 1 To Counter - 2
BirthdayNameList = BirthdayNameList & ", " &
BirthdayList(Counter2)
Next Counter2
BirthdayNameList = BirthdayNameList & " And " & _
BirthdayList(Counter - 1) & " Have Birthday's This Month!"
MsgBox BirthdayNameList
End If

End Sub

Hope this helps.

Bill Horton

"viddom" wrote:

I have two columns, one called NAME and the other BIRTHDAY. I want a MsgBox
that appear when a cell in BIRTHDAY take place during the current month, and
display the name of the person.Something like "JASON, PETER and JOHN HAVE
BIRTHDAY THIS MONTH" . Moreover, I want the MsgBox to pup up as soon as I
open the workbook.

Could somebody help me with the code?

This works perfect, thank you very much

"William Horton" wrote:

Put the below code in the Workbook_Open() event of your workbook.

Also Please...
Change"Book1.xls" to whatever your workbook name is.
Change "Sheet1" to whatever you worksheet name is.
Change "B2:B8" to whatever your range is that has the birthday dates in them.
The below code assumes that the Birthday Names are 1 column to the left of
the birthday dates (Ex. names in column A and dates in column B).

The below code will execute everytime the workbook is opened. If you want
this to work everytime the workbook is "changed" as well you will have to add
this code to the Worksheet_Change event and add a few more lines.

Private Sub Workbook_Open()

Dim BirthdayList() As String, BirthdayNameList As String
Dim Cell As Range
Dim Counter As Integer, Counter2 As Integer

ReDim BirthdayList(0)
For Each Cell In
Workbooks("Book1.xls").Worksheets("Sheet1").Range( "B2:B8")
If IsDate(Cell) Then
If Month(Now) = Month(Cell) Then
BirthdayList(Counter) = Cell.Offset(0, -1).Value
Counter = Counter + 1
ReDim Preserve BirthdayList(UBound(BirthdayList) + 1)
End If
End If
Next Cell

If UBound(BirthdayList) 0 Then
ReDim Preserve BirthdayList(UBound(BirthdayList) - 1)
End If

If Counter = 0 Then
BirthdayNameList = "Nobody Has A Birthday This Month!"
MsgBox BirthdayNameList
ElseIf Counter = 1 Then
BirthdayNameList = BirthdayList(0) & " Has A Birthday This Month!"
MsgBox BirthdayNameList
ElseIf Counter = 2 Then
BirthdayNameList = BirthdayList(0) & " And " & BirthdayList(1) & _
" Have Birthday's This Month!"
MsgBox BirthdayNameList
Else
BirthdayNameList = BirthdayList(0)
For Counter2 = 1 To Counter - 2
BirthdayNameList = BirthdayNameList & ", " &
BirthdayList(Counter2)
Next Counter2
BirthdayNameList = BirthdayNameList & " And " & _
BirthdayList(Counter - 1) & " Have Birthday's This Month!"
MsgBox BirthdayNameList
End If

End Sub

Hope this helps.

Bill Horton

"viddom" wrote:

I have two columns, one called NAME and the other BIRTHDAY. I want a MsgBox
that appear when a cell in BIRTHDAY take place during the current month, and
display the name of the person.Something like "JASON, PETER and JOHN HAVE
BIRTHDAY THIS MONTH" . Moreover, I want the MsgBox to pup up as soon as I
open the workbook.

Could somebody help me with the code?