Stop the deletion of a cell value

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Give this event code solution a try...
Code:
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.
 
Upvote 0
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)"


Code:
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.

Code:
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
 
Upvote 0
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)"
Code:
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...
Code:
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.
 
Upvote 0
Thanks.
So i have applied it like so.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top