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>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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