G
Guest
I have a situation in Excel where I want to restore the formula in a cell in
case of damage. I want to do this because for various reasons I cannot use
standard sheet protection. Also, I'm not really familiar with VBA
programming so I'm really struggeling and getting very little sleep these
nights.....
If you have time, please take a look at my problem and give me some hints,
even if it's just to tell me it's not possible....
I've implemented the following code, restoring formula =C - D if target is
within my "protectarea" (e.g. "E:E"):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Long
If Not Intersect(Target, Range("Protectarea")) Is Nothing Then
rr = Target.Row
Application.EnableEvents = False
Target.Formula = "=C" & rr & " - " & "D" & rr
Application.EnableEvents = True
End If
End Sub
This works fine until I change multiple cells in a range that partly overlap
my "protect area". In such case all cells will of course be changed according
to formula, even though I just want to manipulate those in "protect area".
q1. Is there any way to solve this?
q2. If not, can I prevent the user to even select the cells in Protectarea?
This would actually be a better solution. Is it possible to achive this in
worksheet_selectionchange()? I'm thinking similar to sheetprotection with not
being able to select locked cells, only with sheetprotection switched off
(confused...?).
case of damage. I want to do this because for various reasons I cannot use
standard sheet protection. Also, I'm not really familiar with VBA
programming so I'm really struggeling and getting very little sleep these
nights.....
If you have time, please take a look at my problem and give me some hints,
even if it's just to tell me it's not possible....
I've implemented the following code, restoring formula =C - D if target is
within my "protectarea" (e.g. "E:E"):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Long
If Not Intersect(Target, Range("Protectarea")) Is Nothing Then
rr = Target.Row
Application.EnableEvents = False
Target.Formula = "=C" & rr & " - " & "D" & rr
Application.EnableEvents = True
End If
End Sub
This works fine until I change multiple cells in a range that partly overlap
my "protect area". In such case all cells will of course be changed according
to formula, even though I just want to manipulate those in "protect area".
q1. Is there any way to solve this?
q2. If not, can I prevent the user to even select the cells in Protectarea?
This would actually be a better solution. Is it possible to achive this in
worksheet_selectionchange()? I'm thinking similar to sheetprotection with not
being able to select locked cells, only with sheetprotection switched off
(confused...?).