PDA

View Full Version : Solved: VBA Message Box



gregoryt2002
04-03-2008, 03:15 AM
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
04-03-2008, 05:35 AM
This should work
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

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.
Private Sub Workbook_Open()
Call RangeLoop
End Sub

Hope this helps

mdmackillop
04-03-2008, 06:12 AM
This puts a comment in a corresponding cell, Change to a MsgBox if desired.

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

gregoryt2002
04-03-2008, 06:35 AM
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
04-03-2008, 07:17 AM
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
04-03-2008, 07:25 AM
I've attached some sample data. Im sure im just being a spoon and the code works fine.
Cheers for having a look

mdmackillop
04-03-2008, 07:52 AM
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.

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

gregoryt2002
04-03-2008, 08:00 AM
Is this code easy to change into a pop up message box?

mdmackillop
04-03-2008, 09:08 AM
Yes.
Check how it is done in Post 2

gregoryt2002
04-03-2008, 10:38 AM
Tried to follow the example in Post 2 but still get no message box. Any Ideas?

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

mdmackillop
04-03-2008, 10:59 AM
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

gregoryt2002
04-03-2008, 11:04 AM
Thanks