Help with welcome messages on opening a workbook
Results 1 to 4 of 4

Thread: Help with welcome messages on opening a workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Location
    Northern Drakensberg Mountains, South Africa
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with welcome messages on opening a workbook

    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

  2. #2
    Board Regular WaterGypsy's Avatar
    Join Date
    Jan 2010
    Location
    London, England
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with welcome messages on opening a workbook

    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

  3. #3
    New Member
    Join Date
    Aug 2018
    Location
    Northern Drakensberg Mountains, South Africa
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with welcome messages on opening a workbook

    Would it make a material difference if I used TODAY as opposed to NOW?
    Last edited by Phatt_Chef; Aug 17th, 2018 at 10:18 AM. Reason: spelling

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,392
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Help with welcome messages on opening a workbook

    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:
    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 simon@xxxxxxx.com",, vbCritical, "CALCOM SMALLBus Software Package"
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •