Trigger event between dates

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi,

I'm wanting to trigger an event between certain dates.
I have a named range called "Licence" on the sheet named "Lists". Within "licence is a date. For the full week before this date is met I want a message box to open, after the date I want the code to be bypassed.

I was trying to use the" Now" function but it doesn't seem to work for a range.

I was trying:
If Range("Licence") <= Now + 7 then
Msgbox "Your licence is about to expire"
End if

Thanks in advance for any support you can provide,

Mike
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
690
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this:
VBA Code:
If Range("Licence") >= Date - 7 And Range("Licence") < Date Then MsgBox "Your licence is about to expire"
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,618
Office Version
  1. 365
Platform
  1. Windows
Within "licence is a date.
That indicates to me that "licence" is a range with more than one cell. Is that correct?
If so, what is in the other cells in "licence"?

When is the date going to be checked? Perhaps when you open the workbook?
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
That indicates to me that "licence" is a range with more than one cell. Is that correct?
If so, what is in the other cells in "licence"?

When is the date going to be checked? Perhaps when you open the workbook?
Hi Peter, no it's just one cell that I named Licence.

Yes, I am using the Workbook open to check each time it is opened up.
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi. Try this:
VBA Code:
If Range("Licence") >= Date - 7 And Range("Licence") < Date Then MsgBox "Your licence is about to expire"
Hi Osvaldo,

I tried your code, it would work when the date had past the licence date but not in the date range specified.
I realised though that it was just a wee tweak in the order that was needed so changed it to the following and it works a charm.
Thanks so much for all your help,

Mike

VBA Code:
If Date >= Range("Licence") - 7 And Date < Range("Licence") Then 
MsgBox "Your licence is about to expire"
 
Solution

Forum statistics

Threads
1,141,402
Messages
5,706,232
Members
421,433
Latest member
yash0468

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
Top