+ Reply to Thread
Results 1 to 5 of 5

Locking excel sheet without unlocking previously locked cells

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Smtihfield, RI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Locking excel sheet without unlocking previously locked cells

    I am fairly new to excel programming etc but i am trying to figure out if there is a macro that can acheive this:

    I have a spreadsheet that will continually have users add data to it that needs to be verified. Right now i have all the data locked but when someone wants to lock new information added this requires the sheet to be unlocked and then locked again. Is there a way to lock this new information without unlocking any of the previously locked information?

    Thanks

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Locking excel sheet without unlocking previously locked cells

    You can lock a previously unlocked cell using VBA, but the VBA will need to unlock the sheet to do it, not exactly what you asked, but I think in the spirit of what you're asking. You mean can it be done without manually unlocking/relocking the sheet.

    I would say select all the cells you want to make sure are now locked, then run this macro, being sure to edit it to include your password, or remove the password if there isn't one:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    Smtihfield, RI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Locking excel sheet without unlocking previously locked cells

    I guess what i am trying to acheive is the ability for individuals to add to a spreadsheet and then once an individual completes an entry that row will be locked. Maybe something like column J is a trigger that once something is entered in this column that respective row is locked. I do not want people to be able to unlock the spreadsheet because that will get rid of the integrity of the verification.

  4. #4
    Registered User
    Join Date
    08-06-2012
    Location
    Smtihfield, RI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Locking excel sheet without unlocking previously locked cells

    Although, the more i play around with what you have given me this seems like it will work fine since i will be the only one who knows the password. AND this is so mush simpler than what i was imagining.
    I am open to more suggestions as i will have to trial this a little more to make sure it works for all of our applications but this seems great! thanks

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Locking excel sheet without unlocking previously locked cells

    You CAN put a macro into the sheet itself that is monitoring column J in realtime and locking those cells as entries are made, but that's a very user-UNfriendly thing to do.

    1) Users who make mistakes couldn't fix them before quitting and sending to you.

    2) The macro in the workbook means the workbook changes to an xlsm so the user would have to allow macros to be enabled, the macro I gave you could be stored in your Personal.xlsb file and run from there via hotkey, so it wouldn't need to be IN the main doc at all.


    A third alternative is to create a workbook_close or a workbook_BeforeSave macro that locks every cell in column J that isn't blank before those events, that takes care of problem #1 above, but not problem #2.


    I think YOU locking the data after you've reviewed it is probably better.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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