Prevent excel formulas from getting deleted

Asked By 10 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by Caldwell Llanos
Go To Solution
Answered By 5 points N/A #165906

Prevent excel formulas from getting deleted

qa-featured

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
  • Posted on -
  • Question Category:
Best Answer
Best Answer
Answered By 5 points N/A #165907

Prevent excel formulas from getting deleted

qa-featured
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
  • Posted on -
  • Question Category:

Best solving puzzles apps for Mac

Previous

Website of downloadable digital photo frames Onam-themed

Next

Related Questions