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

Posted on 2014-08-01
Last Modified: 2014-08-04
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.

Question by:sherman6789
    LVL 27

    Assisted Solution

    by:Glenn Ray
    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.

    LVL 10

    Expert Comment

    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
    LVL 27

    Expert Comment

    by:Glenn Ray
    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.

    Author Comment

    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.

    LVL 27

    Accepted Solution

    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).


    Author Closing Comment

    Thanks to Glen Ray.  We are able to complete this task and it is working very well.

    LVL 27

    Expert Comment

    by:Glenn Ray
    I'm glad I was able to least point you in the right direction.  


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
    Local Continuous Replication is a cost effective and quick way of backing up Exchange server data. The following article describes the steps required to configure Local Continuous Replication. Also, the article tells you how to restore from a backup…
    Viewers will learn the basics of formula auditing in Excel 2013.
    Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now