Change values on a protected worksheet?

craigyg

Board Regular
Joined
Dec 14, 2005
Messages
114
I have been running into errors in my code when I try to update or change values on a protected worksheet via the VBA code. Rather than go through my code and first unprotect it, then reprotect after the code executes - i was wondering if there was a better way to go about this. Any ideas?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yes, you can select all the cells you want to be able to change while protected
and from the menu choose Format > Cells > Protection tab and make sure the
'Locked' checkbox is unticked.

[Edit:]
Is there a reason you don't like unprotecting / reprotecting within the code?
 

craigyg

Board Regular
Joined
Dec 14, 2005
Messages
114
Additional related question...

In addition, does anyone know if there is a way to write a class module or something that performs as follows: anytime there is a change to a value on a protected sheet from the code to unprotect the sheet (and reprotect after code execution)? I would love to do it in a class module so I wouldn't have to add my unprotect, protect code to every subroutine that changes a value on a protected sheet.
 

craigyg

Board Regular
Joined
Dec 14, 2005
Messages
114
Protection

Half Ace -

In response to your question - I just thought that constantly protecting and unprotecting the sheets might slow down my code.

Also, your response led me to another question: What if I want to update values on a protected sheet to locked cells? There are definitely times I want the code to change some value in a cell and I don't want the user to be able to change that value themselves by going into the worksheet.
 

Forum statistics

Threads
1,136,430
Messages
5,675,794
Members
419,586
Latest member
RoteichA

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