Message box on certain dates

Kimmerz29

New Member
Joined
Jul 5, 2006
Messages
25
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??
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I created a worksheet that looks like this:
Excel Workbook
ABCDEF
1DateItem to CheckInformedToday:4/6/2009
24/6/2009Item S1
34/9/2009Item P
44/14/2009Item C
54/24/2009Item G
65/2/2009Item W
75/9/2009Item N
85/19/2009Item F
95/25/2009Item W
105/30/2009Item X
116/3/2009Item I
126/9/2009Item J
136/22/2009Item U
146/29/2009Item Z
157/3/2009Item M
167/8/2009Item G
177/16/2009Item Z
187/20/2009Item C
197/30/2009Item W
208/8/2009Item 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:
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
 
Upvote 0

Forum statistics

Threads
1,214,069
Messages
6,117,523
Members
448,765
Latest member
crobehh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top