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

 

[merlin_the_magician]

anybody?

 

[Mudface]

Sorry, was having my tea. Try this, it'll select the cell in the next column or, if that gives an error or the user has selected more than one cell it'll return to cell A1. It's not perfect by any means and under certain circumstances will be a bit confusing but you may be able to work out a better way if it's too much hassle for your users.

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

Set myRange = Range("D15:D16, I12, X33, AB4")
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."
On Error Resume Next
If Target.Count = 1 Then Target.Cells.Offset(0, 1).Select
If Err Or Target.Count > 1 Then Cells(1, 1).Select
On Error GoTo 0
End If

End Sub

 

[merlin_the_magician]

Works wonderfull mudface! Thank you so much.

Just out of sheer curioucity...

how can i insert a second code in the same page? If i continue under 'end sub' an error occurs...

 

[Mudface]

What second code do you want? If it is to run at the same time and under the same event as the existing code, you will need to add it before the End Sub.

 

[merlin_the_magician]

i want to enter a second code, simular to the first one, but with a different text in the messagebox.

I cannot simply copy the entire code... this gives an error. Do i need to use only part of it?

 

[Mudface]

Still not sure what exactly you want to do, but if it's just changing the message then just change the text between the inverted commas after the MsgBox statement: -

MsgBox "Change this text to what you want."

If not, you're going to have to be very specific about what you want to happen in addition to the above and when it should happen (eg by changing cells, selection, pressing a button etc).

I also don't understand why just copying and pasting the code gives an error- is it in the right place (ie the worksheet code module)? All you need to do is copy the code, right-click on a sheet tab, select view code and then paste it over the text that's created for you in the VBA editor. The final code should look exactly like the code above looks.

 

[merlin_the_magician]

Found what was going wrong already...

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

should not be in the code twice.

 

[Mudface]

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

should not be in the code twice.

Indeed not :wink:.