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...?).

Of course.... Thank you so much. You really saved my day!

Best regards
Kathrine

"Vergel Adriano" wrote:

Kathrine,

try it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Long
Dim rng As Range
Dim c As Range

Set rng = Intersect(Target, Range("Protectarea"))
If Not rng Is Nothing Then
For Each c In rng
rr = c.Row
Application.EnableEvents = False
c.Formula = "=C" & rr & " - " & "D" & rr
Application.EnableEvents = True
Next c
End If
End Sub



--
Hope that helps.

Vergel Adriano


"Kathrine" wrote:

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...?).