VBA - allow user to delete a "locked" cell

[Geordie]

Morning All,

I have some formulas which are protected, so Ive put a password on the sheet. Problem is that I want the user to be able to delete rows... when I try and delete a row with the protection on I get the following error.

You are trying to delete a row that contains a locked cell. Locked cells cannot be deleted while the worksheet is protected.

Is there any code I can use to get around this? My protected columns are A and some of D.

Thanks,
Mandy

 

[Jon von der Heyden]

Hello Mandy

Do you want to do this with VBA? You can get your users to call this macro when they want to delete row(s). Change the password to reflect thje one you are currently using. The macro deletes ALL rows based on the selected region.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DelRow()<br><br>ActiveSheet.Unprotect "PASSWORD"<br>    Selection.EntireRow.Delete<br>ActiveSheet.Protect "PASSWORD"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Geordie]

Thanks,

I just accidently deleted some rows.... (Doh)... so figuring if I can easily do it then users can.


How do I add a msg box around this to ask them if they are sure?

M

 

[Jon von der Heyden]

Sure:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DelRow()<br><br><SPAN style="color:#00007F">Dim</SPAN> msg<br><br>msg = MsgBox("Are you sure?", vbYesNo)<br>    <SPAN style="color:#00007F">If</SPAN> msg = vbNo <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>ActiveSheet.Unprotect "PASSWORD"<br>    Selection.EntireRow.Delete<br>ActiveSheet.Protect "PASSWORD"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Geordie]

thanks

Have a great day

M