I have a workbook with a "cover sheet" and a calculations sheet for quoting.
There are cells for pricing materials. I have been asked if there is someway
to create a reminder that would come up every 90 days to check the
materials/component pricing. I'm sure there is a way to do it but it is way
beyond my abilities. If someone could help I sure would appreciate it.
Thank you in advance!

Hi,

One way is to write the date of the last price check in a cell and on
workbook_open check if it is more than 90 days ago and if it is give the
warning and update the date in the cell. This uses A1 of sheet 1 but in
practice you could put it out of the way

Private Sub Workbook_Open()
lastchecked = Sheets("Sheet1").Range("A1")
If DateDiff("d", lastchecked, Now) = 90 Then
MsgBox "Check prices"
Sheets("Sheet1").Range("A1") = Now
End If
End Sub

Mike

"Doug" wrote:

I have a workbook with a "cover sheet" and a calculations sheet for quoting.
There are cells for pricing materials. I have been asked if there is someway
to create a reminder that would come up every 90 days to check the
materials/component pricing. I'm sure there is a way to do it but it is way
beyond my abilities. If someone could help I sure would appreciate it.
Thank you in advance!

Mike,
First of all thanks for the response and example. I want to make sure I
understand: when you enter the date, in say "A1", does that date
automatically update when the 90 days have passed or would you have to
manually change the date in order to have it run another 90 day cycle? Is
there a way to put in a start date, say today, and at the end of the 90 day
period have the date automatically update for the next 90 day cycle?

Thanks


"Mike H" wrote:

Hi,

One way is to write the date of the last price check in a cell and on
workbook_open check if it is more than 90 days ago and if it is give the
warning and update the date in the cell. This uses A1 of sheet 1 but in
practice you could put it out of the way

Private Sub Workbook_Open()
lastchecked = Sheets("Sheet1").Range("A1")
If DateDiff("d", lastchecked, Now) = 90 Then
MsgBox "Check prices"
Sheets("Sheet1").Range("A1") = Now
End If
End Sub

Mike

"Doug" wrote:

I have a workbook with a "cover sheet" and a calculations sheet for quoting.
There are cells for pricing materials. I have been asked if there is someway
to create a reminder that would come up every 90 days to check the
materials/component pricing. I'm sure there is a way to do it but it is way
beyond my abilities. If someone could help I sure would appreciate it.
Thank you in advance!

Doug,

When you pick your cell to store the date, for the first time manually enter
the date of the last price check. That date can be any valid date. From then
on the code executes very time you open the workbook and:-

Tests the date to see if it = 90 days in the past. If TRUE

A message box is displayed reminding of the need to check the prices.
A new date is written to the date cell.

If FALSE then nothing happens.

Mike

"Doug" wrote:

Mike,
First of all thanks for the response and example. I want to make sure I
understand: when you enter the date, in say "A1", does that date
automatically update when the 90 days have passed or would you have to
manually change the date in order to have it run another 90 day cycle? Is
there a way to put in a start date, say today, and at the end of the 90 day
period have the date automatically update for the next 90 day cycle?

Thanks


"Mike H" wrote:

Hi,

One way is to write the date of the last price check in a cell and on
workbook_open check if it is more than 90 days ago and if it is give the
warning and update the date in the cell. This uses A1 of sheet 1 but in
practice you could put it out of the way

Private Sub Workbook_Open()
lastchecked = Sheets("Sheet1").Range("A1")
If DateDiff("d", lastchecked, Now) = 90 Then
MsgBox "Check prices"
Sheets("Sheet1").Range("A1") = Now
End If
End Sub

Mike

"Doug" wrote:

I have a workbook with a "cover sheet" and a calculations sheet for quoting.
There are cells for pricing materials. I have been asked if there is someway
to create a reminder that would come up every 90 days to check the
materials/component pricing. I'm sure there is a way to do it but it is way
beyond my abilities. If someone could help I sure would appreciate it.
Thank you in advance!

Thank you so much for the clarification! I appreciate your help on this.


"Mike H" wrote:

Doug,

When you pick your cell to store the date, for the first time manually enter
the date of the last price check. That date can be any valid date. From then
on the code executes very time you open the workbook and:-

Tests the date to see if it = 90 days in the past. If TRUE

A message box is displayed reminding of the need to check the prices.
A new date is written to the date cell.

If FALSE then nothing happens.

Mike

"Doug" wrote:

Mike,
First of all thanks for the response and example. I want to make sure I
understand: when you enter the date, in say "A1", does that date
automatically update when the 90 days have passed or would you have to
manually change the date in order to have it run another 90 day cycle? Is
there a way to put in a start date, say today, and at the end of the 90 day
period have the date automatically update for the next 90 day cycle?

Thanks


"Mike H" wrote:

Hi,

One way is to write the date of the last price check in a cell and on
workbook_open check if it is more than 90 days ago and if it is give the
warning and update the date in the cell. This uses A1 of sheet 1 but in
practice you could put it out of the way

Private Sub Workbook_Open()
lastchecked = Sheets("Sheet1").Range("A1")
If DateDiff("d", lastchecked, Now) = 90 Then
MsgBox "Check prices"
Sheets("Sheet1").Range("A1") = Now
End If
End Sub

Mike

"Doug" wrote:

I have a workbook with a "cover sheet" and a calculations sheet for quoting.
There are cells for pricing materials. I have been asked if there is someway
to create a reminder that would come up every 90 days to check the
materials/component pricing. I'm sure there is a way to do it but it is way
beyond my abilities. If someone could help I sure would appreciate it.
Thank you in advance!