I am trying to find workarounds for users to insert and delete rows in a protected sheet/table.
I have UserInterFaceOnly:=True and have a simple macro to insert a row at the desired location. This is fine as the row will always insert in the same place, but having a simple macro to delete a row can remove any row (including those that should never be deleted). The best solution i can think of (and one which i like) would be to have a macro that deletes a row if the user selects 'Delete' from a data validation list (of that particular row).
the macro successfully deletes the row but keeps throwing an error back at me (operator type mismatch) and highlights 'If Target.Value = "Delete" Then' part of the macro.
I presume it has an issue with the target value now being deleted?
i didnt want to put in something to ignore the error and end if the macro is fundamentally flawed.
Can anyone help me?
I have UserInterFaceOnly:=True and have a simple macro to insert a row at the desired location. This is fine as the row will always insert in the same place, but having a simple macro to delete a row can remove any row (including those that should never be deleted). The best solution i can think of (and one which i like) would be to have a macro that deletes a row if the user selects 'Delete' from a data validation list (of that particular row).
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ThisRow = Target.Row
If Target.Value = "Delete" Then
' Range("B" & ThisRow).Interior.ColorIndex = 3
' Target.Row("1:1").EntireRow.Select
Target.Rows.EntireRow.Select
Selection.Delete Shift:=xlUp
Else
Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub
the macro successfully deletes the row but keeps throwing an error back at me (operator type mismatch) and highlights 'If Target.Value = "Delete" Then' part of the macro.
I presume it has an issue with the target value now being deleted?
i didnt want to put in something to ignore the error and end if the macro is fundamentally flawed.
Can anyone help me?