Prevent Clear Format for Unlocked Cells in a Protected Sheet

mistersend

New Member
Joined
May 29, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
I have a sheet in which some cells are locked and some cells are unlocked. After protecting the sheet, I want -
1. Any one can change the content of the unlocked cells, but no one can clear the format of the Cells by going to the option HOME > CLEAR > Clear All or Clear Format.
2. If someone copy content from somewhere and paste to the unlocked cells, only the contents should be copied or changed, not the format.

I'm working with .xlsx extention and don't want to use macro or vba.

Thank you!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
What you are asking is not possible without macro or vba.

If the cell you are coping is unprotect then ALL functionality of Excel is available.

Therefor your only option to come close to what you are asking is for the user to select ‘Paste Values’, ‘Paste Formula” when cell is copied
 

mistersend

New Member
Joined
May 29, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Ok...thank you for confirmation!
If macro is a solution for this case, may I know which type of macro will be better for the case?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
As an older gentleman whose eyesight is also aging, I appreciate the ability to increase font size.
What is the bad thing that happens if the user changes the format of a cell? If we knew that, we might be able to prevent the bad thing while allowing the user to adjust the cell format for their definition of comfortable viewing.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,208
Members
417,131
Latest member
Seanr19871

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