Message box on certain dates

[Kimmerz29]

Hi,

I have a spreadsheet that lists equipment for a worksite. These items need checking every fortnight.

I need a forumla/macro that will give me a Message Box when I open the spreadsheet on these specified dates.

For example;
Sheet "RG1" next equipment check is due 15 Apr 09 then 29 Apr 09 and so on(Checked every 2 weeks)

So far I have put all the specified dates for the next few years in a column (F1-F50), and tried creating a macro that would go something like this;

If Worksheets("RG1").Range("f1:f50").Value = Date Then
MsgBox "Please check RG1 Kit"
End If


But so far no such luck - Any idea's??

 

[Sal Paradise]

I created a worksheet that looks like this:

Excel Workbook
	A	B	C	D	E	F
1	Date	Item to Check	Informed		Today:	4/6/2009
2	4/6/2009	Item S	1
3	4/9/2009	Item P
4	4/14/2009	Item C
5	4/24/2009	Item G
6	5/2/2009	Item W
7	5/9/2009	Item N
8	5/19/2009	Item F
9	5/25/2009	Item W
10	5/30/2009	Item X
11	6/3/2009	Item I
12	6/9/2009	Item J
13	6/22/2009	Item U
14	6/29/2009	Item Z
15	7/3/2009	Item M
16	7/8/2009	Item G
17	7/16/2009	Item Z
18	7/20/2009	Item C
19	7/30/2009	Item W
20	8/8/2009	Item X
Sheet1

Dates in A, Items to be reminded about in B, and today's date in F1.

If column C is empty, it will tell you whatever is in column B. You could put equipment names or whatnot in there. Column C will then get filled, and the next time you open, it won't remind you. Feel free to make it remind you 1000 times if you'd like, I just figured you don't want that.

Anyway, here's the code:

Private Sub Workbook_Open()
    Dim rng As Range
    Set rng = Worksheets("Sheet1").Range("A:A").Find(Range("F1").Value, LookIn:=xlValues)
    If Not rng Is Nothing Then
        If rng.Offset(0, 2) = "" Then
            MsgBox rng.Offset(0, 1)
            rng.Offset(0, 2).Value = 1
        End If
    End If
End Sub

 

[Kimmerz29]

Thankyou for that!

It works perfectly