How do we delete rows in Excel while the "sheet" remains locked?

We have designed an Excel file with locked rows.and columns for the staff to use.  They need to be able to add and delete certain rows.  We have found that they are able to add rows but not delete any while the sheet is locked. The users are allowed to entered data only in certain cells.   There are many automatic formulas throughout the sheet.  Of course, locked cells will not allow them to make any changes whereas unlocked cells will allow them to add numbers, text, etc.

We followed the instructions:  In the "Change" area, we clicked "Protect Sheet".  Then we checked "Insert Rows" and "Delete Rows" before we entered the password.

They are able to add rows while the sheet is locked but when they try to "delete" a row, they get the message that "You are not allowed to delete a row that contains a locked cell.  Remove the protection and then delete the row."  We don't want the users to know the password and make changes or delete rows.

Am I doing something wrong?  The boxes are checked that would seem to allow us to add or delete rows after the sheet is locked.  The add rows works but not the delete rows.  Most rows contain locked and unlocked cells.

Any assistance that you can give will be appreciated.  Thanks.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
All cells have the "Locked" attribut by default.  There are most-certainly cells beyond your work area (i.e., columns far to the right) whose cells are locked.  There is no bypassing this; even if you allow new rows to be added while the sheet is protected, they will not be able to be deleted.

You have only two options:
1) Unlock ALL cells in the area where you would allow rows to be deleted.  If new rows are inserted within this range they will also be unlocked and able to be deleted.  However, if you have formulas here, this solution is not practical.
2) Use VBA to control the insertion/deletion of rows within specific areas.  The code would basically unlock the sheet, add or remove a row (and perhaps copy down formulas) and then lock the sheet again.

I use the latter method routinely for form control.

10023Web site maintenance and designCommented:
to GlennRay...if the users were members of a domain..could that allow them to override the protection on the "locked cells" by authentication within the domain
Glenn RayExcel VBA DeveloperCommented:
In the regular Excel environment, Windows Authentication or Domain membership has no bearing on Excel sheet protection or access.  

However, using VBA one can use the above criteria to determine if a user can access or change an Excel worksheet.  The domain can be determined using the ENVIRON("Userdomain") command.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

sherman6789Author Commented:
Thanks for your reply Glen.

Depending on how deficult it will be to write a VBA to delete lines, we will live with the ability to let anyone add lines but they will need to contact someone with the password to delete lines.

Can you give me an idea of how I would go by writing a VBA and where?  

Also what does the "Delete Rows" check box on the "Protect Sheet" mean?  Every line has several locked and unlocked cells.  If the system will not allow us to delete rows that contain locked cells, I would expect it to also not allow us to add rows when the sheet is locked since they all contain locked and unlocked cells.

Glenn RayExcel VBA DeveloperCommented:
1) Delete Rows option:
If you define a row or rows in advance as all having "unlocked" attributes, those rows can be deleted if the sheet if protected.  Similarly, if you allow rows to be inserted in a protected sheet, those newly-inserted rows may also be deleted.

2) VBA option:
    a) You would first need to store a list of usernames that would be allowed this access.  If the list were small (say, less than ten) and not changing frequently, I would just store the names in the VBA routine itself.  If the list were larger, or more-importantly, changing often, I would probably store them in a protected workbook in a common network folder (i.e., a folder that all users of this workbook would also have access..could be the same folder as the workbook itself).
    b) The subroutine would, on opening, check the username of the person opening the file against that defined list.  If the username is on the list, set a flag (ex., boolEditor = TRUE), that will be used to set/reset the protection parameters of the worksheet.  
    c) On workbook close, the sheet would return to the highest level of protection.  That would prevent editing if users opened the workbook without enabling macros.

Here's some sample code...not highly detailed here, but should give you an idea of what's possible.  This would all be stored in the "ThisWorkbook" object in the VBA project:
Option Explicit
Const strpw = "drowss@p"
Dim strEmpID As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Application.DisplayFullScreen = False
End Sub

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
    ActiveSheet.Unprotect Password:=strpw
    strEmpID = Environ("UserDomain") & "\" & Environ("UserName")
    Application.ScreenUpdating = True
End Sub

Private Sub Set_Editing_Level()
    Dim booleditor As Boolean
    Dim x As Integer
    Dim arrUsers(10) As String
    arrUsers(1) = "DOMAIN\user1"
    arrUsers(2) = "DOMAIN\user2"
    arrUsers(3) = "DOMAIN\user3"
    arrUsers(4) = "DOMAIN\user4"
    arrUsers(5) = "DOMAIN\user5"
    arrUsers(6) = "DOMAIN\user6"
    For x = 1 To UBound(arrUsers)
        If strEmpID = arrUsers(x) Then booleditor = True
        Exit For
    Next x

    If booleditor Then
        'unlock sheet protection
        ActiveSheet.Unprotect Password:=strpw
        'set limited editing
        ActiveSheet.Unprotect Password:=strpw
        With ActiveSheet
           .Protect Password:=strpw, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True
           .EnableSelection = xlUnlockedCells
        End With
    End If
End Sub

Open in new window

On open, the user domain and username would be retrieved and then checked against a defined list (in the Set_Editing_Level sub).  If it exists, the active worksheet is unlocked.  Otherwise, the sheet is protected with limited access (rows can be inserted only).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sherman6789Author Commented:
Thanks to Glen Ray.  We are able to complete this task and it is working very well.

Glenn RayExcel VBA DeveloperCommented:
I'm glad I was able to least point you in the right direction.  

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.