Reverse deleting macro

R

rn

I need help with writing a macro that will run when a cell
is deleted. For example if a cell with formula is
accidentally deleted the macro will re-populate this cell.

Thanks.
 
F

Frank Kabel

Hi
you may use the worksheet_change event for this. try something like the
following: (put this in your worksheet module)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
on error goto errhandler
With Target
If .formula="" Then
Application.EnableEvents = False
.formula="SUM(B1:B100)"
End If
End With

errhandler:
Application.EnableEvents = True
End Sub
 
G

Guest

Thanks Frank.

What changes is required to this code if I wanted to use
it for more than one cell, can I also use fill range to
repopulate the cells with the function?


Using your code below it wrote the function as a text, not
a function.
 
R

rn

Frank

I tried ammending your code to be used for more than one
cell. the problem is when I range delete the cells it
does not work.

Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)



If Intersect(Range("A1:A16"), Target) Is Nothing Then Exit
Sub
On Error GoTo errhandler
With Target
If .Formula = "" Then
Application.EnableEvents = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"
Selection.AutoFill Destination:=Range("A1:A16"),
Type:=xlFillDefault
End If
End With

errhandler:
Application.EnableEvents = True


End Sub
 
F

Frank Kabel

Hi
try the following (not tested):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as range
If Intersect(Range("A1:A16"), Target) Is Nothing Then Exit Sub
On Error GoTo errhandler
Application.EnableEvents = False
for each cell in Intersect(Range("A1:A16"), Target)
If cell.Formula = "" Then
cell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"
End If
next
errhandler:
Application.EnableEvents = True
End Sub
 
D

Dave Peterson

You have another reply at your other post.

I need help with writing a macro that will run when a cell
is deleted. For example if a cell with formula is
accidentally deleted the macro will re-populate this cell.

Thanks.
Click to expand...
 
Top