Ensuring a cell stay unlocked in a locked sheet

stanco

New Member
Joined
Mar 16, 2019
Messages
47
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
Hi Stanco,

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

1611832486424.png
 

stanco

New Member
Joined
Mar 16, 2019
Messages
47
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
No. I still can't make the cell locked.
 

stanco

New Member
Joined
Mar 16, 2019
Messages
47

ADVERTISEMENT

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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
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.
 

stanco

New Member
Joined
Mar 16, 2019
Messages
47

ADVERTISEMENT

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.
 

stanco

New Member
Joined
Mar 16, 2019
Messages
47
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.
oh, and can you try copying some text from elsewhere other than excel? can be from word, your outlook or even a random word here and paste it onto A10/ A16.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

stanco

New Member
Joined
Mar 16, 2019
Messages
47
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,586
Messages
5,637,225
Members
416,963
Latest member
samfuge

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
Top