Lock worksheet and display message after X Days

  • Hi,

    I wish to put expiry date to my sheet.

    after 30 days (from the signed day) i want excel to prompt that information on sheet1 has expired.

    Upon expiry, all fields in sheet1 are to be blocked and no changes allowed.

    I tried to use the IF function but not successful.

    is there any other ways? maybe using macro?

    Thanks

  • Re: Put sheet to expired in 30 days


    To do this automatically you will need to use VBA (macro). You will need to put a date into one cell on the Worksheet (can be hidden if you don't want it seen) that is the signing date. Then use the Workbook Open event to check the current date against the signed date and to protect the sheet if the current date is 30 or more days after.

    In the Workbook code module:

    Code
    Option Explicit
     
    Private Sub Workbook_Open()
     
        With ThisWorkbook.Sheets("SignedSheet")
            If Date > (.Range("B1") + 30) Then .Protect Password:="Insecure"
        End With
     
    End Sub



    Be aware that this is a VERY basic implementation and there are some issues to this technique:

    1) If the user disables Macros then the code will not run and no protecting will take place. This cannot be avoided by any legitimate means.
    2) If the user does not save the Workbook after the protection then the protection is not saved. Although it will be re-applied when the open the Workbook (allowing for condition 1 above).
    3) If the user changes the date on their PC they can avoid the protection taking place. Although if the Workbook has already had the sheet protected and has been saved then this will not remove the protection.
    4) Excel Worksheet passwords can easily be cracked in a few minutes.

    However this is the basic technique for doing what you asked. Test example attached.

    [EDIT]

    Just realised I left off the prompt to tell the user it is happening. Sigh.

    Change the code to be:

  • Re: Put sheet to expired in 30 days


    Hi Rob,

    thank you for your prompt reply.

    However, i encountered some problem.

    When i click on any fields on sheet1, i didnt see the massage that page has expired.

    And another thing is that, on that sheet there are some attachment.

    i hope user can see the attachement but not able to edit any information on that sheet.

    maybe a

  • Re: Put sheet to expired in 30 days


    The reason it did not protect the worksheet I sent in the example was because I set todays date as the Signed date. The idea was for you to either change the date on your PC to 30+ days from now or to change the signed date on the worksheet to an earlier date. Save, close and re-open. I thought it was obvious, but I should have explained it clearly.

    Returning your example with the Workbook Close code attached having modified it to suit your example (Sheet1 instead of SignedSheet and H9 instead of B1). This will pop up a message as soon as you open the workbook (because the Signed date is 01/01/2010). The level of protection applied means you can still select cells on the Worksheet but you cannot change them. Since the cell on your example simply contains the text Attachement, I cannot test for the exact attachment you have, but it should be ok.

  • Re: Lock worksheet and display message after X Days


    WORK_10,


    Just to make you aware:
    Excel/VBA is not a secure platform. By design, when opening workbooks containing VBA code, users are prompted to enable macros, which can elect not to do and thereby defeat any and all code in the workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!