MsgBox when user tries to delete a row

Question

I am building a tool in excel and have protected the workbook so that deleting or inserting rows/columns is not allowed, using the code below:

Sub ProtectSheet()
ActiveSheet.Protect _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingHyperlinks:=True, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=True, _
    AllowFiltering:=True, _
    AllowUsingPivotTables:=True, _
    Password:=cPassword
End Sub

However, I also want a message box to appear if the user tries to delete or insert a row/column that tells them they cannot do this and provides other instructions. Can anyone advise me on how to do this?

Thanks!

how about using a `MsgBox` before your code runs ? – Shai Rado Jan 14 '17 at 17:30 — Shai Rado, Jan 14 '17 at 17:30

score 0 · Accepted Answer · answered Jan 14 '17 at 18:00

You can use the selection change event like this (code to go in the worksheet's code module):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Address = .EntireRow.Address Or .Address = .EntireColumn.Address Then
            MsgBox "You can not insert or delete any row or column", vbInformation, "FYI"
        End If
    End With
End Sub

If they try to insert or delete by selecting entire rows or columns (which is how I typically go about doing that) then this message box will pop up, but it won't pop up if you select smaller ranges of cells.

Wow, thanks so much! That was exactly what I needed. – Kristen Jan 14 '17 at 20:08 — Kristen, Jan 14 '17 at 20:08

MsgBox when user tries to delete a row

1 Answers1