Stop the deletion of a cell value

[ipbr21054]

Hi,
One of my staff is forgetting NOT to delete certain cell values.

Example.
Cell E9 has this code in it =IF(C9="","",C9+D9)
When he deletes values in Row 9 he also then deletes the code that is in cell E9.
Next time values are entered into row 9 the calculation does not take place as this particular user deleted it.

What do you advise to overcome this please.
I was thinking of a msgbox but then there is the chance he would say yes to delete it still.

 

[Rick Rothstein]

Give this event code solution a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E9")) Is Nothing Then
    Application.EnableEvents = False
    Range("E9").Formula = "=IF(C9="""","""",C9+D9)"
    Application.EnableEvents = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ipbr21054]

Hi,
That works fine thanks.

What if i needed to apply this to the column.
Taking the cell reference from the last post how would it be written.
Ive entered what i can but not sure about the part "=IF(C9="""","""",C9+D9)"

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C9="""","""",C9+D9)"
    Application.EnableEvents = True
  End If
End Sub

I also have a Private Sub Worksheet_Change(ByVal Target As Range) as shown below so i would need to insert your code into it,if you could avise please.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub


'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 3 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "C") = Abs(Cells(Target.Row, "C")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "C").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If


End Sub

 

[Rick Rothstein]

What if i needed to apply this to the column.
Taking the cell reference from the last post how would it be written.
Ive entered what i can but not sure about the part "=IF(C9="""","""",C9+D9)"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C9="""","""",C9+D9)"
    Application.EnableEvents = True
  End If
End Sub

Try it like this...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C4="""","""",C4+D4)"
    Application.EnableEvents = True
  End If
End Sub

I also have a Private Sub Worksheet_Change(ByVal Target As Range) as shown below so i would need to insert your code into it,if you could advise please.

You should be able to put the body of the code (from the If to the End If) at the top of your current Change event procedure.

 

[ipbr21054]

Thanks.
So i have applied it like so.

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C4="""","""",C4+D4)"
    Application.EnableEvents = True
  End If
  
'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub


'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 3 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "C") = Abs(Cells(Target.Row, "C")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "C").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If


End Sub