VBA - Delete Row On Protected Sheet

Camilla1980

New Member
Joined
Mar 22, 2018
Messages
13
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
758
Office Version
2016
Platform
Windows
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

Code:
ActiveSheet.Unprotect (password)
and this at the end of your code

Code:
ActiveSheet.Protect (password)
note that you can provide exceptions to the sheet protection
the list can be found here
 

Camilla1980

New Member
Joined
Mar 22, 2018
Messages
13
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

Well-known Member
Joined
Jan 26, 2015
Messages
758
Office Version
2016
Platform
Windows
Many thanks for the speedy reply, BlakeSkate, but can you just confirm this is where the additions the code should sit

here ya go
Code:
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

New Member
Joined
Mar 22, 2018
Messages
13
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,041
Messages
5,412,012
Members
403,408
Latest member
Matt_314

This Week's Hot Topics

Top