title: Preventing users from deleting a row in a particular table with a certain value in a particular column in Excel
body: <p>This may be similar to <a href="https://stackoverflow.com/questions/7769653/prevent-user-from-deleting-certain-rows-based-on-contents-of-a-cell-in-that-row">this question</a> but I believe it goes a step further in complexity which is why I asked it.</p>
  
  <p><strong>Context:</strong> I'm building a budget spreadsheet that can create and delete rows from tables. In the sheet I have two tables. One contains totals based on category, while the other table contains the transactions the user can enter in to populate the totals in the other table. I protected the worksheet to keep users from breaking the formulas and only have the cells they should edit (i.e. input values in) unprotected. I also have macros to insert and delete one or multiple rows on a table (I coded the macros to unprotect/protect the worksheet before and after the macro is finished running).</p>
  
  <p><strong>Problem:</strong> My question deals with the <a href="https://i.stack.imgur.com/7IzRH.png" rel="nofollow noreferrer">first table</a>. In that table, I want to ensure that the "Deposits" row cannot be deleted. Question is, in my code, how can I ensure the user can delete all other rows in another table that contains "Deposits" while preventing deletion of the "Deposits" row in this table? I'm thinking of the following pseudo code, but feel free to make other suggestions:</p>
  
  <pre><code>'If selected range contains cells in Column A
  'and cell in selected range = Deposits
  'Then pop error message
  'Exit Sub
  </code></pre>
  
  <p>And here is the code I have for my delete macro</p>
  
  <pre><code>Sub DeleteRow()
  '
  ' DeleteRow Macro
  '
  ' Keyboard Shortcut: Ctrl+Shift+D
  '
  
  Dim loTtest         As ListObject
  Dim loSet           As ListObject
  Dim c               As Range
  Dim arrRows()       As Variant
  Dim arrTemp()       As Variant
  Dim xFind           As Variant
  Dim iCnt            As Long
  Dim sMsg            As String
  
  ActiveSheet.Unprotect Password:="PYS"
  Erase arrRows()
  iCnt = 1
  For Each c In Selection.Cells
      If Not c.ListObject Is Nothing Then
          If loSet Is Nothing Then
              Set loSet = c.ListObject
          Else
              If c.ListObject &lt;&gt; loSet Then
                  'different table
                  MsgBox "You have more than one table selected.", vbInformation, "ERROR!"
                  ActiveSheet.Protect Password:="PYS"
                  GoTo MyExit
              End If
          End If
  
          If iCnt = 1 Then
              ReDim arrRows(1 To iCnt)
              arrRows(iCnt) = c.Row - loSet.HeaderRowRange.Row
              iCnt = iCnt + 1
          Else
              On Error Resume Next
              xFind = 0
              xFind = WorksheetFunction.Match(c.Row - loSet.HeaderRowRange.Row, arrRows(), 0)
              If xFind = 0 Then
                  ReDim Preserve arrRows(1 To iCnt)
                  arrRows(iCnt) = c.Row - loSet.HeaderRowRange.Row
                  iCnt = iCnt + 1
              End If
              Err.Clear
              On Error GoTo 0
          End If
  
      Else
          'a cell is not in a table
          MsgBox "Your selection is all or partially outside of a table.", vbInformation, "ERROR!"
          ActiveSheet.Protect Password:="PYS"
          GoTo MyExit
      End If
  Next c
  
  Call SortArray(arrRows())
  sMsg = "Are you sure you want to delete " &amp; UBound(arrRows) &amp; " rows from from this table?"
  If MsgBox(sMsg, vbYesNo + vbDefaultButton2, "CONTINUE?") &lt;&gt; vbYes Then
      ActiveSheet.Protect Password:="PYS"
      Exit Sub
  End If
  
  For iCnt = UBound(arrRows) To LBound(arrRows) Step -1
  
      loSet.ListRows(arrRows(iCnt)).Delete
  Next iCnt
  ActiveSheet.Protect Password:="PYS"
  Exit Sub
  
  MyExit:
  
  End Sub
  
  
  
  Sub SortArray(MyArray() As Variant)
  
  Dim iStart          As Long
  Dim iEnd            As Long
  Dim iStep           As Long
  Dim iMove           As Long
  Dim vTemp           As Variant
  
  iStart = LBound(MyArray)
  iEnd = UBound(MyArray)
  For iStep = iStart To iEnd - 1
      For iMove = iStep + 1 To iEnd
          If MyArray(iStep) &gt; MyArray(iMove) Then
              vTemp = MyArray(iMove)
              MyArray(iMove) = MyArray(iStep)
              MyArray(iStep) = vTemp
          End If
      Next iMove
  Next iStep
  
  End Sub
  </code></pre>
  
  <p>By the way, I didn't come up with all of this myself; I piecemealed most of this code. :) Let me know if you need any more info or context. Thanks in advance!</p>
  
  <p><a href="https://drive.google.com/file/d/0B0ZBMZsVFP5_U1M4MDZCeHB1WEE/view?usp=sharing" rel="nofollow noreferrer">Here is the link to the budget workbook.</a></p>