Delete rows while sheet protected

[loulou]

I have a worksheet protected, cells locked, and password protected. Other
users have access to this file and need to be able to insert rows and delete
rows. However, they are having trouble deleting rows. Is there a way to fix
this or is it more trouble then it is worth? I am trying to protect my
formulas I have worked so hard on.

Any help will be useful

 

[Dave Peterson]

In xl2002+, you can let the user insert/delete rows on a protected worksheet.

Under Tools|Protection|protect sheet (xl2003 menus), there's a couple of options
that can be checked.

 

[loulou]

To be more specific, we have xp. We can protect the sheet and check the boxes
allowing all users to insert and delete rows however, they can insert but
can't delete.

 

[Gord Dibben]

That is correct.

You can only delete a row in which all cells are unlocked.

Insert doesn't care about locked cells so you can insert rows.

You must unprotect the sheet, delete the selected rows then re-protect.

Sub delete_rows()
ActiveSheet.Unprotect Password:="yourpassword"
Selection.EntireRow.Delete
ActiveSheet.Protect Password:="jyourpassword"
End Sub

Give your users a button for deleting rows.


Gord Dibben MS Excel MVP