Help with welcome messages on opening a workbook

Phatt_Chef

New Member
Joined
Aug 10, 2018
Messages
11
Hi All,
Some advice from a more learned soul than this aging chef please....

I have a welcome message that pops up on opening a work book:
"GOOD DAY and WELCOME
PLEASE NOTE:
IF YOU HAVE NOT PURCHASED AN OPERATOR AND OWNER KEY
YOU WILL NOT BE ABLE TO ACCESS THE PROGRAM ON THE EXPIRY OF YOUR TRIAL PERIOD"

My code as follows:
Private Sub Workbook_Open()


'Sends a message on opening the WORKBOOK that the expiry date will soon be reached.


MsgBox "GOOD DAY and WELCOME" & vbNewLine & "PLEASE NOTE:" & vbNewLine & "IF YOU HAVE NOT PURCHASED AN OPERATOR AND OWNER KEY" & vbNewLine & "YOU WILL NOT BE ABLE TO ACCESS THE PROGRAM ON THE EXPIRY OF YOUR TRIAL PERIOD.", vbCritical, "CALCOM SMALLBus Software Package"


End Sub

AND, it works perfectly, but what I want it to do is when a certain date is reached it will pop up with another message, eg 30, 20, 10, 5, 2 days before expiry date it says:


"Your trial period for this Software expires in 10 days time. If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com)"

My code I have tried, but that doesn't work is as follows:
Private Sub Workbook_Open()

'Sends a message on opening the WORKBOOK that the expiry date will soon be reached.



Dim c1 As Range
Set c1 = ThisWorkbook.Sheets("INPUT_DATA").Range("lbl.EXPIRY_DATE")
If IsDate(c1) Then
If Now+30 = c1 Then
MsgBox "Your trial period for this Software expires in 30 days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"

If Now+20 = c1 Then

MsgBox "Your trial period for this Software expires in 20 days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"

If Now+10 = c1 Then
MsgBox "Your trial period for this Software expires in 10 days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"

If Now+5 = c1 Then
MsgBox "Your trial period for this Software expires in 5 days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"

If Now+2 = c1 Then
MsgBox "Your trial period for this Software expires in 2 days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"

End If
End If
End If
End If
End If
End If


End Sub

I have written a code to run on expiry as follows that does lock the work sheets, but it must be on each sheet. Is it possible to have one piece of code that is relevant to all the worksheets ie the workbook.??
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'This runs the code to seal the worksheet at the end of the trial period (Where A1:ZZ25000 is the area of each worksheet that may or may not have data, but effectively renders the worksheets useless)


If Not Intersect(Target, Range("A1:ZZ25000")) Is Nothing Then
If Date > "lbl.Expiry_Date"Then
Application.EnableEvents = False
MsgBox "YOUR TRIAL PERIOD HAS EXPIRED! " & vbNewLine & "No entry in any cells is permitted. To unlock, purchase a key from XXXXX ", vbCritical, "CALCOM SMALLBus Software Package"
Range("A1").Select
Application.EnableEvents = True
End If
End If
End Sub

All input is welcome.
The workbook has 25 sheets, numbered 01 through 25 with names

Thanks in anticipation


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
Try changing the tests to greater than or equal, I think Now() has a time element so you are unlikely to get an exact match
 

Phatt_Chef

New Member
Joined
Aug 10, 2018
Messages
11
Would it make a material difference if I used TODAY as opposed to NOW?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
Using "equal" like you originally did will not work if you are using NOW, as NOW has a time component, and DATE does not.
You would need to use the method WaterGypsy recommend if using NOW.

Also, the way you have written your code, I don't think it is going to show anything of days 3 and 4 to expiration. Is that really what you want?
If you want to show the number of days until expiration, forget all the IF statements and just use something like:
Code:
[COLOR=#333333]MsgBox "Your trial period for this Software expires in " & c1 - Date &  " days time." & vbNewLine & "If you wish to purchases a licence, please contact XXXX XXXX (082xxxxxxx) or [/COLOR][EMAIL="simon@xxxxxxx.com"]simon@xxxxxxx.com[/EMAIL][COLOR=#333333]",, vbCritical, "CALCOM SMALLBus Software Package"[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,102,447
Messages
5,486,950
Members
407,574
Latest member
Greso

This Week's Hot Topics

Top