VBA - Delete Row On Protected Sheet

[Camilla1980]

Hi,
I have an auto sheet event script running on my workbook which deletes a row from sheet 1 and moves it to 2 other worksheets when completed:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("N:N")) Is Nothing And Target.Value = "Yes" Then
If MsgBox("Completed?", vbYesNo) = vbNo Then Exit Sub
If Not Evaluate("isref('" & Range("E" & Target.Row) & "'!$B$1)") Then
MsgBox "sheet " & Range("E" & Target.Row).Value & " does not exist"
Exit Sub
End If
With Rows(Target.Row)
.Copy Sheets(Range("E" & Target.Row).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
.Copy Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
End If
End Sub

I now need to protect the formula in one column but this will stop the row from being deleted unless I unprotect the sheet.

Can I add to the code so that it unprotects the sheet (say with a password, Secret) and once the action is completed, it protects the sheet again?

If it's possible, where would it fit in my code?

My knowledge of VBA and such is absolutely zero, so any help would be really appreciated!

Thanks
Camilla

 

[DataBlake]

yes your sheet can be unprotected and reprotected with a password and all you would have to do is is place this at the beginning of your code

ActiveSheet.Unprotect (password)

and this at the end of your code

ActiveSheet.Protect (password)

note that you can provide exceptions to the sheet protection
the list can be found here

 

[Camilla1980]

Many thanks for the speedy reply, BlakeSkate, but can you just confirm this is where the additions the code should sit:

ActiveSheet.Unprotect (password)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("N:N")) Is Nothing And Target.Value = "Yes" Then
If MsgBox("Completed?", vbYesNo) = vbNo Then Exit Sub
If Not Evaluate("isref('" & Range("E" & Target.Row) & "'!$B$1)") Then
MsgBox "sheet " & Range("E" & Target.Row).Value & " does not exist"
Exit Sub
End If
With Rows(Target.Row)
.Copy Sheets(Range("E" & Target.Row).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
.Copy Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
End If
End Sub
ActiveSheet.Protect (password)

Thanks again!

 

[DataBlake]

Many thanks for the speedy reply, BlakeSkate, but can you just confirm this is where the additions the code should sit

here ya go

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("N:N")) Is Nothing And Target.Value = "Yes" Then
If MsgBox("Completed?", vbYesNo) = vbNo Then Exit Sub
If Not Evaluate("isref('" & Range("E" & Target.Row) & "'!$B$1)") Then
MsgBox "sheet " & Range("E" & Target.Row).Value & " does not exist"
Exit Sub
End If
With Rows(Target.Row)
.Copy Sheets(Range("E" & Target.Row).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
.Copy Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
End If
ActiveSheet.Protect (secret)
End Sub

just change the password to whatever you set the password when you protect the sheet
also just to note in your code that this code only activates when a change has been made to a cell. so if your protected sheet does not allow cells to be changed...well....this won't work
the reason being is that excel won't let you change the cell so your code never runs in order to unprotect. there are options to get around this however
you can:

1. change this entire sub to be run when calling a sub instead of running it when the sheet is changed
2. prompt a password to unprotect the sheet with a different method (such as a button, running a macro, or a keyboard shortcut(
3. you can set the password prompt to happen whenever you something else on the sheet (click a cell, open the sheet, etc)
4. enable changes when you protect a sheet (but that will probably defeat the purpose of protecting it in the first place)

let me know which method you would like to use

 

[Camilla1980]

So sorry - I've just realised I hadn't thanked you!

The code works perfectly for me without any modifications - I only have one column in the worksheet protected, so changes can still be made across the rest of the sheet.

So a massive thanks for sharing your knowledge and taking the time to help!