Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset cell after change
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...?). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell change, reset formula? | Excel Worksheet Functions | |||
Set cell to record date when adjacent cell is filled AND NOT RESET | Excel Worksheet Functions | |||
Reset Form/Hide Rows based on cell value change | Excel Programming | |||
how do you reset your arrow keys to move cell to cell? | New Users to Excel | |||
How do you reset 'change was made in workbook' switch? | Excel Programming |