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

 

[dstrat6]

You could protect your sheet or create a message box that is tiggered by a certain action made by the user.

Hope this helps.

Regaurds,
dstrat6

 

[nieironsio]

Hello

thanks, can't think of an action that covers off the delete on the keyboard side of things, as usual i suspect it may be my lack of ability to think laterally that is hindering me here. I want to avoid the protection route as other than deleteing the user needs to have full use of the SS.

thanks

 

[dstrat6]

I'm not sure if this will work but I know that the computer reads every button as a number. Figure out what number delete is then omit it in your program. That way when the user tries to delete something the button won't work. If thats what your tring to acomplish. I'd try a google search for the binary digits of the keyboard. Then go from there.

 

[nieironsio]

i'll give it a go

thanks

 

[xlbo]

Use the worksheet CHANGE event and the worksheet SELECTION CHANGE event

Capture the value initially in the selection change event and compare it to the value in the TARGET cell in the change event. If Target.Value = "" and PriorValue<> "" then pop up the message box:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PriorValue = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
dim theAnswer
If PriorValue is nothing then PriorValue = ""

If Target.Value = "" and PriorValue <> "" then
  theAnswer = msgbox ("Do you really want to delete that?", vbyesno+vbinformation,"Deletion")
  if theAnswer = vbno then
     application.enableevents = false
     target.value = PriorValue
     application.enableevents = true
  end if
end if
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Because there is no Before_Delete event, the only way I can think of doing this is to use a Worksheet_Change event.

Test to see if the selection is empty - if it is, then the user must have just deleted something since we know they made a change.

Use a msgbox to confirm. If they answer NO, then undo the delete.

Try this:

Sub Worksheet_Change(ByVal Target As Range)
Dim MyMsg, Mytitle As String
MyMsg = "Are you sure you want to delete this data?"
Mytitle = "Delete Confirmation"

    If IsEmpty(ActiveCell) Then
        myval = MsgBox(MyMsg, vbYesNo + vbQuestion + vbDefaultButton2, Mytitle)
            If myval = vbNo Then
                Application.Undo
            End If
    End If
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Dang. Too slow.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

lol - need to speed up your typing John!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

John,

Just to be picky. In your code:

Dim [blue]MyMsg[/blue], Mytitle As String

You do know MyMsg is Variant, not a String.

Try:

Dim MyMsg [blue]As String[/blue], Mytitle As String

Have fun.

---- Andy

 

[nieironsio]

Looks good - thanks guys - haven't been ablr to try out yet but i have faith

THanks

 

[SkipVought]

"...reads every button as a number..."

There are more ways to DELETE that hitting the DELETE button.

Protecting your sheet can prevent deletions.

Skip,

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

 

[nieironsio]

AnotherHiggins

Hello, i've usde your code and it works perfectly but - the only prob is that it displays the message when you make any worksheet change as well as deletion, i thought it would be quite simple to tweak but have thus far failed. any thoughts?

thanks

N

 

[nieironsio]

xlbo

Hello - i have tried your code but cannot get it going - keep getting error surrounding the priorvalue line??

Thanks for the help btw

 

[electricpete]

A small modification to the code from anotherhiggins - works during my tests:

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 = "Are you sure you want to delete this data?"
    Mytitle = "Delete Confirmation"

    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, vbYesNo + vbQuestion + vbDefaultButton2, Mytitle)
        If myval = vbNo Then
            Application.Undo
        End If
    End If
End Sub

 

[nieironsio]

thats great thanks - i'm definately getting there now - only problem is that you can overwrite with this one. but i'll attempt to modify

thanks

 

[xlbo]

which priorvalue line?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

apologies - try this - tested:

Public PriorValue

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PriorValue = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theAnswer
If IsEmpty(PriorValue) Then PriorValue = ""

If Target.Value = "" And PriorValue <> "" Then
  theAnswer = MsgBox("Do you really want to delete that?", vbYesNo + vbInformation, "Deletion")
  If theAnswer = vbNo Then
     Application.EnableEvents = False
     Target.Value = PriorValue
     Application.EnableEvents = True
  End If
End If
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nieironsio]

ah thats working now

thanks v much

 

[electricpete]

One thing with the most recent code posted above, if you paste into an area larger than one cell, you will get a type error when it tries to evaluate

If Target.Value = ""...

It seems like it had a few other glitches. Seems like I could sometimes cut/paste write on top of new data without getting any flag. Doesn't quite seem reproducible though... I'm not sure why. I have excel 2000 on windows XP.

How is the variables PriorValue = Target.Value retained in memory after Worksheet_SelectionChange terminates?