A warning to users when deleting data

[nieironsio]

Hello

I have created a .xls for multiple users; it seems that some users have accidentally delted data from the SS in the past, is there a way i can have maybe use a userform that warns users when any data is about to be be deleted and request a confirmation, this would include using delete on the keyboard.

Thanks

Nie

 

[WalkerEvans]

I'm a little confused regarding the definition of "deleting data". Does this mean strictly deleting information, or does it include over-writing information? If the latter, the code submitted so far will not protect your data at all. It will simply take the new data and the test for deletion will fail.

Somehow, your original post gives me the impression that you want to prevent any data currently in the file from disappearing; that would imply that over-writing would also be bad. Am I on the wrong track here? (That happens more often that I like!) If not you should revisit Skip's suggestion about protecting the sheet, or at least get some code that covers both options.




----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was repossessed."

 

[ck1999]

WalkerEvans,

Could you add an if statement to say

if targetvalue <> priorvalue then
'current if statement and code
end if

This would check to be sure no changes had been made

ck1999

 

[SkipVought]

"if you paste into an area larger than one cell, you will get a type error when it tries to evaluate "

dim t as range
for each t in target
  if t.value = "" then....
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[electricpete]

Thanks skip. I had tried to address the same thing in my code above using a double-loop over rowindex and colindex... yours is more elegant and compact.

 

[techie131]

I modified this code slightly so it simply blocks all changes to cells with data and only allows you to add data to cells that are currently empty. The problem I am having is that if you accidently try to delete a cell or row that is empty it makes a loop forcing you to hit OK over and over again. I have the code below can anyone tell me how I can modify it to get rid of this loop

Private Sub Worksheet_Change(ByVal target As Range)

Dim MyMsg As String, Mytitle As String
Dim deleteflag As Boolean
Dim rowindex As Integer
Dim colindex As Integer
MyMsg = "No Deleting"
Mytitle = "No Deleting"

deleteflag = True
For rowindex = 1 To target.Rows.Count
For colindex = 1 To target.Columns.Count
If target.Cells(rowindex, colindex) <> "" Then deleteflag = False
Next colindex
Next rowindex
If deleteflag Then
myval = MsgBox(MyMsg, vbNo + vbQuestion + vbDefaultButton2, Mytitle)
If myval = vbNo Then
Application.Undo
Else
Application.Undo
End If
End If


If target.Count > 1 Then
PrevVal = ""
Exit Sub
Else
If Not Intersect(target, [a:Z]) Is Nothing Then
If PrevVal <> "" Then
target.Value = PrevVal
End If
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)


If target.Count > 1 Then
PrevVal = ""
Exit Sub
Else
If Not Intersect(target, [a:Z]) Is Nothing Then
PrevVal = target.Value
End If
End If
End Sub