http://pubs.logicalexpressions.com/P...cle.asp?ID=808
Reading your requirement again, I don't think you are necessarily asking for Validation, but rather ensuring your orginal formulas remain unchanged.
As indicated in previous comments, deleting a row, column, or individual cell will still change the basic spreadsheet.
But, you can enter the following code into the Worksheet module for the sheet you wish to maintain the formulas.
All I did was modify the approach in the above link.
Option Explicit
Dim prevVal As String
Const tRng = "B2,B10,F11,E26,B22" ' Change this non-contiguous range to fit your needs.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range(tRng), Target) Is Nothing Then
prevVal = Target.FormulaR1C1
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(tRng), Target) Is Nothing Then
Target.FormulaR1C1 = prevVal
End If
' Disable the event by typing "_BAK" (or whatever to change the name)
' between the last letter in "Change" and the opening parenthesis.
' This allows the original formula to be changed.
' After changing remember to delete the "_BAK" so the event will work again
End Sub
Hopefully this will also resolve the second question posed at http://www.excelforum.com/showthread...05732#poststop
Bookmarks