• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2182
  • Last Modified:

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.

  • 4
  • 2
2 Solutions
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.

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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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 help...at least point you in the right direction.  


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now