Prevent excel formulas from getting deleted

Asked By smithy willu 10 points N/A Posted on - 06/01/2013

Need a serious excel formula knowledge people here.

I had created an awesome spreadsheet that is that bloody good that was spread to my officemates. I have a few formulas-cells that display specific info-text when selected, and display specific error-message when data is entered, because I limited input to ZERO characters, so overwriting is not possible. Entering values is impossible, because of the validation, however it is possible to delete the formula.

How can I prevent the formulas from getting deleted?

I DO NOT want to use common password-protection, because I want to specify the error message myself by using validation.

Anybody knows how to do this?

• Status: Open
• Question Views: 818
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Caldwell Llanos

Go To Solution

Answered By Mullins Debord 5 points N/A #165906

Prevent excel formulas from getting deleted

Hello,

Yeah, I understand what you are saying. You create this perfect database then someone alters it and it is no longer serving its purpose. I would advise you to use the password protect but it seems that does not amuse you so try this code.

Private Sub Worksheet_SelectionChange (ByVal Target As Excel. Range)

If Target.Count > 1 Then Exit Sub
If Target.HasFormula = True Then
MsgBox "You cannot change this cell. This cell calculates the sum of cells A1:A100."
Target.Offset(0, 1).Select
End If

End Sub

About Mullins Debord

Questions
0

Answers
100

Best Answers
26

• Vote Up 0 Vote Down

• Posted on - 06/01/2013
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Caldwell Llanos 5 points N/A #165907

Prevent excel formulas from getting deleted

Hi Smithy, 

 

Hope you’re already able to find a workaround with your issue. By the way, the first suggestion really works but it involves you knowing how to work with Macros. Just in case you are still in search of some option and Macro is not your first option then here’s my suggestion. 

 

I understand that “Protecting” the sheet may not be your option but for me this is the easiest way to approach it. What I have in mind really is to lock the cells, make the formula hidden and then don’t let the users select the fields with formulas in it. Still protect the sheet but you don’t need to put in a password. This is the most effective way I can think of that does not require too much effort. 

 

Hope this helps!

 

About Caldwell Llanos

Questions
1

Answers
100

Best Answers
49

• Vote Up 0 Vote Down

• Posted on - 06/02/2013
• Question Category: MS Excel