+ Reply to Thread
Results 1 to 6 of 6

VBA to manage workbook expiry date allowing for extensions

  1. #1
    Registered User
    Join Date
    09-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    VBA to manage workbook expiry date allowing for extensions

    Hi all,
    I hope you are well and staying safe.

    I have been working on what should be a relatively straightforward piece of code but I can't get it right and I don't have much hair left to pull out!
    Can someone take a look at the below please?

    What I am trying to do is load a workbook, check today's date versus a defined expiry date (exdate), if the expiry date is in the future then continue to open the workbook. If it is in the past (i.e. workbook expired) then I would like two options, one is an admin password ("test" in the below text) to let me in, the other would be a password to extend the life ("extend" in the below text). My intention would be to permit three extensions (stored in a named range/cell called ExtensionCount) and each time the "extend" password is used the macro will +1 onto the cell value. If you go over 3 extensions then the macro would inform you via a msgbox and exit, if you are 3 extensions or below it lets you in.
    I apologise in advance for my bad code, I'm chopping up someone else's solution to a simpler problem. I feel like it's pretty close but when I enter the extend password it is closing the workbook, I think I'm getting confused with the IFs and when to End If.

    Thanks for any advice you could offer.


    Please Login or Register  to view this content.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,852

    Re: VBA to manage workbook expiry date allowing for extensions

    Hi and welcome

    Give this a try:
    Please Login or Register  to view this content.
    BSB

  3. #3
    Registered User
    Join Date
    09-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: VBA to manage workbook expiry date allowing for extensions

    Quote Originally Posted by BadlySpelledBuoy View Post
    Hi and welcome

    Give this a try:
    Please Login or Register  to view this content.
    BSB
    Perfect!
    Thanks for the input, really appreciate it.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,852

    Re: VBA to manage workbook expiry date allowing for extensions

    As someone with very little hair of his own left to pull out, I'm more than happy to help

    Have a good day!

    BSB

  5. #5
    Registered User
    Join Date
    03-28-2022
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    1

    Re: VBA to manage workbook expiry date allowing for extensions

    Great job.

    Wanna ask one thing. If user opens the file after the "exdate" he has to write the password in every opening the file?
    Is there a possibility to add code that if he once write correct password there will be no need in writing it again e.g. another year form "exdate" time.
    thanks in advance.
    W.

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,852

    Re: VBA to manage workbook expiry date allowing for extensions

    You could store exdate in a cell somewhere then as for a password, if the user gets the password correct simply update the cell to show a later date.

    Something like this, although I've not tested it.
    Please Login or Register  to view this content.
    BSB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Highlighting Expiry dates but with dates that have passed the expiry duration, not date.
    By smurf54454 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2019, 06:04 AM
  2. [SOLVED] Count days before expiry date & after Expiry date in one formula
    By Macfool in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2017, 02:58 PM
  3. [SOLVED] Date extensions
    By Judylily in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2017, 03:02 PM
  4. [SOLVED] Workbook Expiry date with eviron name bypass
    By Macfool in forum Excel General
    Replies: 5
    Last Post: 12-01-2016, 01:06 PM
  5. autocalculate expiry date / expiry date colour warnings
    By kimbling1 in forum Excel General
    Replies: 8
    Last Post: 08-27-2014, 02:53 AM
  6. Handling Workbook Extensions
    By Ascension in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-29-2010, 12:47 PM
  7. Replies: 1
    Last Post: 06-08-2006, 09:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1