Solved: VBA Message Box

[gregoryt2002]

I am making a workbook that loads staff data off of a website and displays it in Excel.
I have a field called Length of service and want to automatically load a message box to tell the user if there is anyone who is coming up to a 5/10/15 etc years of service.
the raw data is already in the workbook so it will just be a case of checking in a column for any of those years.

[georgiboy]

This should work
[vba]Sub RangeLoop()


Dim rCell As Range, MyRange As Range, CelVal As String
Set MyRange = Worksheets("Sheet1").Range("A1:A1000")
For Each rCell In MyRange.Cells

If rCell.Value + 1839 = Date Then
rCell.Select
CelVal = rCell.Offset(0, 1).Value
MsgBox CelVal & " has been here nearly 5 years"
End If

If rCell.Value + 3665 = Date Then
rCell.Select
CelVal = rCell.Offset(0, 1).Value
MsgBox CelVal & " has been here nearly 10 years"
End If

If rCell.Value + 5489 = Date Then
rCell.Select
CelVal = rCell.Offset(0, 1).Value
MsgBox CelVal & " has been here nearly 15 years"
End If
Next rCell

End Sub[/vba]

its a bit rough about a week and a halfs notice but you could tinker

you could also put this in the workbook to call it when you open the workbook.
[vba]Private Sub Workbook_Open()
Call RangeLoop
End Sub[/vba]

Hope this helps

[mdmackillop]

This puts a comment in a corresponding cell, Change to a MsgBox if desired.
[VBA]
Option Explicit
Sub RangeLoop()
Dim rCell As Range, MyRange As Range
Dim YrDate As Date
Const NoticePeriod = 21 '<=== change to suit

Set MyRange = Worksheets("Sheet1").Range("A1:A1000")
For Each rCell In MyRange
YrDate = CDate(Day(rCell) & "/" & Month(rCell) & "/" & Year(Date))
If YrDate - Date > 0 And YrDate - Date < NoticePeriod Then
Select Case Year(Date) - Year(rCell)
Case 5
rCell.Offset(, 3) = "5 years"
Case 10
rCell.Offset(, 3) = "10 years"
Case 15
rCell.Offset(, 3) = "15 years"
End Select
End If
Next rCell
End Sub

[/VBA]

[gregoryt2002]

Sub RangeLoop()
Dim rCell As Range, MyRange As Range
Dim YrDate As Date
Const NoticePeriod = 7

Set MyRange = Worksheets("Raw_Data").Range("L:L")
For Each rCell In MyRange
YrDate = CDate(Day(rCell) & "/" & Month(rCell) & "/" & Year(Date))
If YrDate - Date > 0 And YrDate - Date < NoticePeriod Then
Select Case Year(Date) - Year(rCell)
Case 5
rCell.Offset(, 3) = "5 years"
Case 10
rCell.Offset(, 3) = "10 years"
Case 15
rCell.Offset(, 3) = "15 years"
End Select
End If
Next rCell
End Sub

I seem to get an error on the highlighted line. Im a complete VBA novice so im sure its probably something im doign.

[mdmackillop]

Can you post some sample data so I can check this out. It may be that your dates are in a text format. Use Manage Attachments in the Go Advanced section

[gregoryt2002]

I've attached some sample data. Im sure im just being a spoon and the code works fine.
Cheers for having a look

[mdmackillop]

The code failed where the cell contents were not valid dates. This should fix it. Note that setting the constant to 1 will show a result only on the anniversary date.
[VBA]
Sub RangeLoop()
Dim rCell As Range, MyRange As Range
Dim YrDate As Date
Const NoticePeriod = 10
Set MyRange = Worksheets("Raw_Data").Range("B:B")
For Each rCell In MyRange
If IsDate(rCell) Then
YrDate = CDate(Day(rCell) & "/" & Month(rCell) & "/" & Year(Date))
If YrDate - Date > 0 And YrDate - Date < NoticePeriod Then
Select Case Year(Date) - Year(rCell)
Case 5
rCell.Offset(, 3) = "5 years"
Case 10
rCell.Offset(, 3) = "10 years"
Case 15
rCell.Offset(, 3) = "15 years"
End Select
End If
End If
Next rCell
End Sub

[/VBA]

[gregoryt2002]

Is this code easy to change into a pop up message box?

[mdmackillop]

Yes.
Check how it is done in Post 2

[gregoryt2002]

Tried to follow the example in Post 2 but still get no message box. Any Ideas?

[VBA]Sub RangeLoop()
Dim rCell As Range, MyRange As Range
Dim YrDate As Date
Const NoticePeriod = 1
Set MyRange = Worksheets("Raw_Data").Range("L:L")
For Each rCell In MyRange
If IsDate(rCell) Then
YrDate = CDate(Day(rCell) & "/" & Month(rCell) & "/" & Year(Date))
If YrDate - Date > 0 And YrDate - Date < NoticePeriod Then
Select Case Year(Date) - Year(rCell)
Case 5
CelVal = rCell.Offset(, 3).Value
MsgBox CelVal & "5 years"
Case 10
CelVal = rCell.Offset(, 3).Value
MsgBox CelVal & "10 years"
Case 15
CelVal = rCell.Offset(, 3).Value
MsgBox CelVal & "15 years"
End Select
End If
End If
Next rCell
End Sub[/VBA]

[mdmackillop]

[VBA] Select Case Year(Date) - Year(rCell)
Case 5
MsgBox "Celebrating 5 years"
Case 10
MsgBox "Celebrating 10 years"
Case 15
MsgBox "Celebrating 15 years"
End Select
[/VBA]

[gregoryt2002]

Thanks