prevent deleting formula by validation

[merlin_the_magician]

need some serious Excel-knowledge here people…

I’m very proud of myself, for creating an awesome spreadsheet that is that bloody good it will be spread to everyone throughout the office … one minor thing however…

I have a few formula-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 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 know how to do this? Been searching the web, but no result…
This message was edited by merlin_the_magician on 2002-08-01 04:31

 

[Mudface]

I guess you could use a little bit of code, like that below, but to be honest, you're much better off locking the cells you don't want to be changed and protecting the sheet- that's what it's there for after all .

Right-click on the sheet tab, select view code and paste this in. This will prevent anyone from selecting a cell in column A, so you'll probably need to adapt it to your own needs.
<code>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Column = 1 Then
MsgBox "You cannot change this cell."
Cells(Target.row, 2).Select
End If

End Sub
</code>

 

[merlin_the_magician]

By this one will prohibit people from selecting a cell. This can be done by security too.

What i want is people to be able to select the cell, because validated text tells people what the cell does.

I just do not want them to be able to enter any charcter (validated the cell to ZERO characters).
Thing is... altough altering the formula is not possible, deleting however is.

When people try to delete the formula after all, i want to give them a specific error message, telling them why they cannot delete the formula. This does not work with common security, because this will give a standard popup.

 

[Mudface]

Just include the text you want to display when people do select the cell in the MsgBox statement. They can still enter the cell, they just can't do anything while they're there and once they click OK on the pop-up they'll be dumped back out to the cell next to it. As an example change to the below and try it out on a blank workbook: -

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Column = 1 Then
MsgBox "You cannot change this cell. This cell calculates the sum of cells A1:A100."
Cells(Target.row, 2).Select
End If

End Sub

 

[Mudface]

Another way, I guess, would be to unlock the cells you want people to be able to change, Insert Comment on one of the formula cells, then copy this and paste special- comments to the rest of your formula cells.

 

[merlin_the_magician]

YES, yes.... this is what i am looking for!

One more thing... how to specify a CELL instead of a column?

 

[Mudface]

Try this one, it'll work on any cell which has a formula in it. You might want to add some logical statements if you have different formulas, etc and make sure it'll never select a cell which already has a formula in it.

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
This message was edited by Mudface on 2002-08-01 07:01

 

[merlin_the_magician]

I love it!!!

But... when i want to protect a specific cell the following line does not work:

If Target.Address = $A$1 Then

Excel chockes on the '$' sign...

 

[Mudface]

Sorry, ignore that and my edited post above, that wouldn't work properly if you selected a group of cells including your formula cell. Try the following (hopefully last ) piece of code, change myRange to reflect the cell(s) you want: -

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim myRange As Range

Set myRange = Range("A1")
Set isect = Application.Intersect(myRange, Target)
If Not isect Is Nothing Then
MsgBox "You cannot change this cell. This cell calculates the sum of cells A1:A100."
Cells(1, 2).Select
End If

End Sub

 

[merlin_the_magician]

This is exactly what i meant!

(i might be getting a pain in the neck now... but one more question...)

How to specify several cells that are spread out? (so not just D15 and D16, but also I12, X33 and AB4)
This message was edited by merlin_the_magician on 2002-08-01 07:50