Macro to disallow delete action

[rn119]

So I have this worksheet full of formulas that I do not want users to accidentally delete. Now, I know I can use the standard "Protect Sheet" option to not trigger any accidental changes but I would like an additional safety check (since management users will have access to unlock the sheet) via a macro to prompt the user with a message box (with Yes / No options) which essentially asks the user if they are sure they want to delete a cell if the "Delete" button is chosen? If the answer is No, nothing will happen. But if the answer is Yes, it will delete the contents in the cell.

Some help with a macro on this?

 

[CodeNinja]

I used the worksheet change action in a similar way not too long ago....

Dim bUNDONE As Boolean 'used to make sure we do not have multiple undo's for 1 action

Private Sub Worksheet_Activate()
bUNDONE = False 'set bundone to false.  It needs to always start false.
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

Dim sUndo As String

'test to see if the user deleted or added a row.
If Target.Rows(1).Cells.Count = Columns.Count Then
    'bundone is used to make sure we only ask and undo one time.  If we don't have bundone
    'when the program undoes the remove row, we will fire the worksheet change event again and
    'the user will get confused because there will be multiple times it asks if we want to undo.
    'Bundone always starts false.  Make it true for the first call then change it back.
    If bUNDONE = False Then
        bUNDONE = True
    Else
        bUNDONE = False
    End If
    
    If bUNDONE = True Then 'the user deleted a row and we have not yet asked if they want to do this
        s = MsgBox("ALERT!!!! You have deleted or added a row." & vbLf & _
          "Removing or adding rows may affect the functionality of the program." & vbLf & _
          "Are you sure you want to make this change?  (click Yes to make change, click No to undo.)", vbYesNo, Title:="ALERT:")
    End If
    If s = vbNo And bUNDONE = True Then 'the user changed their mind and does not want to delete
        Application.Undo
    Else
        If s = vbYes Then bUNDONE = False 'if the user deleted and did not change His/Her mind, this will correct bundone.
    End If
    
End If
End Sub

All you need to do differently is to test for a deletion of a cell and fire the msgbox/application undo.


If you need help with that let me know,

CN