+ Reply to Thread
Results 1 to 7 of 7

Need to lock a sheet within a workbook after specific date

  1. #1
    Registered User
    Join Date
    12-28-2017
    Location
    Orlando, FL
    MS-Off Ver
    2007
    Posts
    4

    Need to lock a sheet within a workbook after specific date

    Hello,

    I'm using Excel to track the inventory of store locations and the store managers have to put in numbers for inventory received, sold, wasted and on hand every week. I'm looking for a way to have the sheet automatically lock so it can't be edited 2-3 days after the day they do the inventory. I don't want the cells to lock as soon as they put the numbers in so if they make mistakes they can fix it without having to call a district manager to unlock the sheet. I've tried a few methods that I found online but can't seem to get it to work. I'm starting from scratch on these vba codes since I don't know enough about it to know what I did wrong and how to fix it.

    Thank you,
    Hew

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Need to lock a sheet within a workbook after specific date

    So, what code snippets have you tried and in what way are they not working for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-28-2017
    Location
    Orlando, FL
    MS-Off Ver
    2007
    Posts
    4

    Re: Need to lock a sheet within a workbook after specific date

    This is what I have at the moment and it seemed to work as I compiled it and debugged it without getting any error messages but it doesn't lock the sheet. I tried testing it with a date that had already passed but got no results.



    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Need to lock a sheet within a workbook after specific date

    So generally speaking, what you are posing in terms of "locking the sheet/workbook at a specific date/time" would require VBA if you were to do it as you have posed. I would however say that doing this with VBA is likely to cause more issues than it fixes and is easily bypassed using any of the built in protection features of Excel.

    I would recommend a different approach, which depends on what is done with the files after they are "locked". Does someone else review them? Do they send them to someone? Does someone keep them long term for records?

    What I would have in mind is putting a button on the sheet (or in the ribbon..either addin or code in their personal workbook) and making it part of their process to click the button when inventory is "complete". This button could either send a copy to a particular person or group via email or save a copy of it in a shared location (like a network drive) were they have read/write but NOT modify permissions. In either fashion they have then provided a copy to another party and any change they make in their own copy would need to be "resubmitted" to that other party which would then be reviewed and either accepted or not. I am of course presuming that someone other than the person creating inventory is involved otherwise why lock a file only that person looks at.

    The benefits above are that the person updating the sheet can do so without any locks or issues, they simply hit a button when they feel they are done and thats it as far as they are concerned. The recipient (if done via email) or whomever is responsible for keeping tabs on these reports can then review it and consult with the person whom updated if need be. If the person updating it needs to make changes they just hit the button again and the other party reviews it again.

    By having a working copy for the person doing inventory and a separate "static" copy provided to another party you effectively accomplish what I presume your idea of locking would accomplish, which is preventing people from making retroactive changes to solve/hide problems.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  5. #5
    Registered User
    Join Date
    12-28-2017
    Location
    Orlando, FL
    MS-Off Ver
    2007
    Posts
    4

    Re: Need to lock a sheet within a workbook after specific date

    The issue with that is we have stores in multiple states and they are not networked together. The stores have to manually add the workbook file to an email and send it to the corporate office. As they have original of the workbook it needs to be set up so they can't change things on each sheet after a certain date for each sheet. I can lock the VBA so they can't change it and I can password protect each sheet so they can't edit it without the password. I thought about doing it the way you suggested but it just won't work for us.

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Need to lock a sheet within a workbook after specific date

    As I mentioned, you could have the "button" send via email.

    The problem with protection is its a safety measure, not a security measure. If they save the file as xlsx, macros stripped wont work. If they open the file disabling events (hold shift and click the file) the macro wont trigger. If they open the VBA code they can find the password, macros useless then. If they are prompted to enable macros when they open the file and dont, macro wont do anything.

    The above is just the ways they can easily prevent the lock from happening. If you locked the sheet I could unlock it with a text editor in under a minute, regardless of the length of password you use. If you protected the VBA module I could unlock it with a hex editor, again in about 1 minute regardless of password length.

    These protections are meant to help avoid mistakes, not to keep content safe.
    Last edited by AliGW; 12-28-2017 at 12:19 PM. Reason: Unnecessary quotation deleted.

  7. #7
    Registered User
    Join Date
    12-28-2017
    Location
    Orlando, FL
    MS-Off Ver
    2007
    Posts
    4

    Re: Need to lock a sheet within a workbook after specific date

    I've already got it setup so they can't access the VBA code and I am working on a way to hide all the sheets but the one that is for the current week. Everything I'm doing is so there is redundancy in the protection of the sheet. I will also be setting it so they have read/write permissions but not permission to modify the worksheet.

+ 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. vba help needed open workbook search date and lock colums from A to date column
    By prasadavasare in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2015, 01:50 AM
  2. [SOLVED] Macro to unlock sheet, copy to new workbook, then re-lock original sheet
    By Foos Master in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-08-2015, 09:16 PM
  3. [SOLVED] Lock Sheet (With specific settings..)
    By jcabroxo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2015, 12:10 PM
  4. Set range of cells in multiple sheets to lock after a specific date
    By miasha in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2014, 07:33 PM
  5. Import specific sheet with name and Date mm/dd/yyyy from closed workbook
    By frank35 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2013, 11:21 AM
  6. Replies: 3
    Last Post: 06-30-2010, 09:46 AM
  7. Lock sheet tabs option in a specific workbook
    By Tarique in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2006, 11:55 AM

Tags for this Thread

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