protect Excel formulas

Topic

New Reply

How do I prevent other users from entering anything at all into certain cells that contain formulas?

Reply | Quote

Replies

Select your formula cells – Format/Cells/Protection and check the Locked option – Then to activate it, you have to protect the worksheet – Tools / Protection / Protect Sheet. Unfortunately, this brings with it many restrictions like not being able to apply formatting on the sheet etc.

Reply | Quote

the thing that strikes me as perhaps the most annoying and least expected about turning on protection is the file bloat (i’ve seen up to 50% increases).

Brooke

Reply | Quote

I don’t know if this can be of any help, but to prevent users to enter or change something in specific cells – instead of unlocking cells and protecting the sheet – you can use the worksheet_change event, like this (Suppose there is a formula in cell “C5”, then you can prevent someone of changing its contents by putting the following code in the worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range(“C5”) Then
MsgBox “Sorry you are not allowed to edit this cell”
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

You need to disable the application-events, by putting application.enableevents = false before you do the application.undo otherwise you will end up in a continuous loop of change-events. Once the last change has been undone, you need to reset the application-events to true.

Reply | Quote

Would this method be affected by the user disabling macros?

Brooke

Reply | Quote

Yes, you need to enable macros, otherwise it will not work.
What you can do is protect your sheet before save (this can be done by protecting the sheet in the workbook_beforeclose event, so that it cannot be changed if the macros are disabled during opening. The first thing you do after enabling the macros is to fire a small macro to unprotect the sheet. This can be done in the workbook_open event of thisworkbook. I hope this does not sound to chaotic, I am just writing it down by heart. Anyway, give it a try, you will enjoy all these events Excel is providing you.

Simply add
ActiveSheet.UnProtect to the workbook_open event (of course be sure that the activesheet is the one with the change-event macros)

Reply | Quote

Thank you sooooooo much – I’ll try it. In the meantime, I advised the person who wanted my advice to select those cells she didn’t want anyone to be able to change and had her apply a data validation to them whereby she specified an allowable entry to be something no one would ever want to enter with a warning not to type in that cell.

Reply | Quote

Just forgot to mention that I am using these events mainly for validation purposes. It is clear that you can write your own validation rules in this worksheet_change event procedure. e.g. if the value in the cell equal to Target is not between two predefined values, you fire your own warning message and empty the cell with a simple e.g. Range(“C5”).clearcontents.

Reply | Quote