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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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!


 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top