Ensuring a cell stay unlocked in a locked sheet

stanco

New Member
Joined
Mar 16, 2019
Messages
48
I have a file with A10 (name) and A16 (ID number) that users can key in either cell to do a search. the sheet is locked except for the two cells.

the file is working fine until i realised that if user did a ctrl c and ctrl v onto either A10 or A16, it will override the protection of the cell and caused the cell(s) to be locked. i have a marco that is supposed to clear the results by deleting the values in A10 and A16 and because of this, would not work now.

is there any way to ensure that these two cells stay unlocked regardless what the other users did? would be even better if the cell can be reverted to its original formatting (cell colour, alignment and etc.) or is there any VBA code that can revert the file to its original version (like as if it was just downloaded fresh off the website)?



this file is used by hundreds of people and educating all of them seems rather impossible. (I even had to inform a few users that they need to enable editing and enable marco before using the file.)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi Stanco,

I'm not seeing the same behavior but what happens if you only allow "Select unlocked cells" when you protect?

1611832486424.png
 
Upvote 0
Hi Stanco,

I'm not seeing the same behavior but what happens if you only allow "Select unlocked cells" when you protect?

View attachment 30698

Hi Toadstool, i replicated my file and uploaded a copy for your viewing please.

when user type in A10 or A16 and search, the result will be displayed from B22 onward (it is not working because i did not include the database in this file). and you can notice that the alignment is middle aligned.

however, if and when user copy some text from another file and paste onto the cell directly, you will notice that it becomes bottom align and the cell will become a locked cell. hence when user click "clear", the macro is unable to delete the values in A10 or A16.

i hope this is clearer.

p/s: this file is not locked.
 
Upvote 0
No. I still can't make the cell locked.

sorry, i think i didnt make a good job explaining. here i go again.

1) in normal situation, the entire sheet is protected and only A10 and A16 are unlocked for users to input.
1.PNG


2) it will work fine if user type in normally (you can know that they typed as the cell is middle aligned).
2.PNG


3) but when user copy text from another file and pasted onto either A10 or A16 directly, the cell will become locked and it will become bottom aligned.
4.PNG


4) this resulted in my macro to clear (by deleting A10 and A16) to not work.
3.PNG




what i want to know is whether if there is any VBA code to ensure that A10 and A16 will always remain unlocked regardless what the user did.

i hope this is clearer.
 
Upvote 0
I understand what you're saying but I can't reproduce.
Even if I add a sheet, make a cell locked and then copy and paste into A10 it's still unlocked.

Your VBA is password protected so I can't look in there but I don't see why that should have any effect.
 
Upvote 0
I understand what you're saying but I can't reproduce.
Even if I add a sheet, make a cell locked and then copy and paste into A10 it's still unlocked.

Your VBA is password protected so I can't look in there but I don't see why that should have any effect.

oops, so sorry about that. forgot that i locked the VBA.

anyway i have unlocked it and re-uploaded a copy for your viewing please.
 
Upvote 0
Sorry Stanco but I just can't recreate your challenge.

I guess you could always unlock the cells again in the macro?

VBA Code:
Sheets("Sheet1").Unprotect
Range("A10").Locked = False
Range("A16").Locked = False
Sheets("Sheet1").Protect
 
Upvote 0
Solution
Sorry Stanco but I just can't recreate your challenge.

I guess you could always unlock the cells again in the macro?

VBA Code:
Sheets("Sheet1").Unprotect
Range("A10").Locked = False
Range("A16").Locked = False
Sheets("Sheet1").Protect

hi Toadstool, no worries but this code actually helps. I managed to use it to resolve my problem.

have a good weekend!
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top