VBA delete rows with worksheet protection

[basgrom]

Hello,


I use the VBA code below to search and remove a specific row in worksheet shD (Factuur_overzicht) based on the value in worksheet shS (Factuur). It works perfectly without worksheet protection.


Butt the VBA code give an error message when the worksheets are protected. Specific on the deleting part.
I have experimented with AllowDeletingRows and AllowFiltering, but I cannot get it right.


Can you help me with this?


Thanks
Bas

----

Sub Delete_Rows_Based_On_Value()


Dim shS As Worksheet
Dim shD As Worksheet

Set shS = Worksheets("factuur")
Set shD = Worksheets("factuur_overzicht")


'Clear any existing filters
On Error Resume Next
shD.ShowAllData
On Error GoTo 0


'1. Apply Filter
shD.Range("A19:G1000").AutoFilter Field:=2, Criteria1:=shS.Range("L17").Value

'2. Delete Rows
Application.DisplayAlerts = False
shD.Range("A19:G1000").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

'3. Clear Filter
On Error Resume Next
shD.ShowAllData
On Error GoTo 0



End Sub

 

[steve the fish]

Hi. Yes you can unprotect the sheet at the top and protect it again once you have deleted. For example

With Sheets("Sheet1")
    .Unprotect
    .Rows(1).Delete Shift:=xlUp
    .Protect
End With

 

[Fluff]

You need to unprotect the sheet first.
If you need the code for that, just use the recorder whilst you unprotect & than re-protect the sheet.

 

[basgrom]

Thanks, its working now

This does the trick:


--


shD.Unprotect "password"


My vba code


shD.protect "password"