• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Any code to prevent cells from being deleted?

Pasadu

Member
Dear Sir, I have an input in cells A1 and B1. I do not want it to be deleted. If not for locking the cells, is there any code that could prevent cells with input from being deleted? Thank You.
 

rollis13

Member
Have a try with what I came up. Done some testing but needs more to check if for dummies.
To be pasted in the sheet's module.
Code:
Option Explicit
Private UndoA1
Private UndoB1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then
        Application.EnableEvents = False
        Range("A1") = UndoA1
        Range("B1") = UndoB1
        Application.EnableEvents = True
        Exit Sub
    End If
    Application.EnableEvents = False
    Select Case Target.Address
        Case Is = "$A$1"
            If UndoA1 <> "" Then
                Application.Undo
            Else
                UndoA1 = Target.Value
            End If
        Case Is = "$B$1"
            If UndoB1 <> "" Then
                Application.Undo
            Else
                UndoB1 = Target.Value
            End If
    End Select
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    Select Case Target.Address
        Case Is = "$A$1"
            UndoA1 = Target.Value
        Case Is = "$B$1"
            UndoB1 = Target.Value
    End Select
End Sub
 

rollis13

Member
but it did not work
isn't a constructive response, as said I tested it; so ... where did you paste the macros ? what other macros do you have in the project ? You said that A1 and B1 where input cells (manual unput) or are they calculated cells (with formulas) ?
Please, open a new workbook, paste my macros in Sheet1's vbe module and retry using cells A1 and B1 of that sheet.
 

Pasadu

Member
isn't a constructive response, as said I tested it; so ... where did you paste the macros ? what other macros do you have in the project ? You said that A1 and B1 where input cells (manual unput) or are they calculated cells (with formulas) ?
Please, open a new workbook, paste my macros in Sheet1's vbe module and retry using cells A1 and B1 of that sheet.
I have opened a new workbook, pasted it into the sheet module, but i did not get it though. The cells have formula please. Thank you for your response though
 

rollis13

Member
Had another try, please test it. I'm assuming that there are 2 formulas in A1 and B1, so:
In a Standard module paste these 2 global variables:
Code:
Global UndoA1
Global UndoB1
then in the sheet's module paste this:
Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then Exit Sub
    Select Case Target.Address
        Case Is = "$A$1"
            UndoA1 = Target.Formula
        Case Is = "$B$1"
            UndoB1 = Target.Formula
    End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    End If
    Application.EnableEvents = False
    Select Case Target.Address
        Case Is = "$A$1"
            Range("A1") = UndoA1
        Case Is = "$B$1"
            Range("B1") = UndoB1
    End Select
    Application.EnableEvents = True
End Sub
Note that you will not be able to perform a multicell paste or delete whenever one of cells A1 or B1 is in the selection.
Till now the only issue I managed to create is when trying modify a multicell selection when A1 or B1 has the focus.
 

Pasadu

Member
Had another try, please test it. I'm assuming that there are 2 formulas in A1 and B1, so:
In a Standard module paste these 2 global variables:
Code:
Global UndoA1
Global UndoB1
then in the sheet's module paste this:
Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then Exit Sub
    Select Case Target.Address
        Case Is = "$A$1"
            UndoA1 = Target.Formula
        Case Is = "$B$1"
            UndoB1 = Target.Formula
    End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    If Target.Cells.CountLarge > 1 Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    End If
    Application.EnableEvents = False
    Select Case Target.Address
        Case Is = "$A$1"
            Range("A1") = UndoA1
        Case Is = "$B$1"
            Range("B1") = UndoB1
    End Select
    Application.EnableEvents = True
End Sub
Note that you will not be able to perform a multicell paste or delete whenever one of cells A1 or B1 is in the selection.
Till now the only issue I managed to create is when trying modify a multicell selection when A1 or B1 has the focus.
Thank You, I will test i and give you the feedback
 
Top