xld Thanks for this it works a treat I just have a slight problem..
If there is a date due it produces the pop up 12 times..
Code im using is
Private Sub workbook_open() Dim msg As String
Dim lastrow As Long
Dim i As Long
With Worksheets("Jan")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Feb")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Mar")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
With Worksheets("Apr")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "A").Value >= Date And .Cells(i, "A").Value < Date + 7 Then
msg = msg & vbTab & .Cells(i, "B").Value & " off on " & Format(.Cells(i, "A").Value, "ddd dd mmm") & vbNewLine
End If
Next i
If msg <> "" Then
MsgBox "Leaders off within the next 7 days:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Who"
'Else
'MsgBox "No-one off within next 7 days", vbOKOnly, "Leader Absence"
End If
End With
End Sub
ETC ETC
So, whilst it picks up the correct month. It just shows the pop up several times and i'm not sure why.. can you shed any light.