Locking excel sheet without unlocking previously locked cells

**ashileekay** · 08-06-2012, 08:35 AM

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

**JBeaucaire** · 08-06-2012, 09:17 AM

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.

**ashileekay** · 08-06-2012, 09:34 AM

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.

**ashileekay** · 08-06-2012, 09:54 AM

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

**JBeaucaire** · 08-06-2012, 10:23 AM

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.