Excel 2016 VBA Unprotect error for single user


New Member
Aug 12, 2020
Office Version
  1. 2016
  1. Windows
Good Evening,

I’m wondering if someone could cast an eye over some VBA code I am using in a workbook.

The workbook isn’t marked as shared and is protected, it is stored on a network drive so users can access it.

Users all use the same version of Excel 2016 (RemoteApp hosted on a Remote Desktop Session Host Server)

When the workbook is opened the sheet normally updates an access log in the background, hides all the sheets except the landing page. This has various buttons and macros which allows the users to select the sheets they want to view.

This works for all users who have access except for one user which received the following error when the workbook is opened:

Run-time error ‘1004’:
Unprotect method of Worksheet class failed.

When I debug the code with VBA logged in as this user it gets to line 6 being highlighted (the unprotect line) then bugs out with the another error:

Run-time error ‘1004’:
Application-defined or Object-defines error

The user has all the same access permissions to the document as the other users however I can’t for the life of me figure out the error.

All the other macros and buttons still work perfectly it only seems to be the unprotect and protect codes that have issues for this user.

I’ve attached the code below so hopefully someone can make sense.

Wasn’t sure how much people would need so apologies if too much or not enough.

Thanks in advance for any help!

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets("Log").Visible = xlSheetVisible
Dim Lrow As Single
Sheets("Log").Unprotect Password:="*********"
Lrow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Log").Range("A" & Lrow).Value = "Open workbook"
Worksheets("Log").Range("B" & Lrow).Value = Now
Worksheets("Log").Range("C" & Lrow).Value = Environ("USERNAME")
Worksheets("Log").Range("D" & Lrow).Value = Environ("COMPUTERNAME")
Sheets("Log").Protect Password:="**********"
Worksheets("AccDet").Visible = xlSheetVeryHidden
Worksheets("IPAdd").Visible = xlSheetVeryHidden
Worksheets("Assets").Visible = xlSheetVeryHidden
Worksheets("Resource").Visible = xlSheetVeryHidden
Worksheets("FobCard").Visible = xlSheetVeryHidden
Worksheets("Events").Visible = xlSheetVeryHidden
Worksheets("RemoteApp").Visible = xlSheetVeryHidden
Worksheets("CGroups").Visible = xlSheetVeryHidden
Worksheets("UGroups").Visible = xlSheetVeryHidden
Worksheets("COMPorts").Visible = xlSheetVeryHidden
Worksheets("HDDs").Visible = xlSheetVeryHidden
Worksheets("ChangeLog").Visible = xlSheetVeryHidden
Worksheets("Log").Visible = xlSheetVeryHidden
Worksheets("Landing").Visible = xlSheetVisible
Application.ScreenUpdating = True
Call Auto_Save
End Sub

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.


Well-known Member
May 24, 2005
The code looks OK
Can the person manually unprotect the workbook?
If the person attempts to access the workbook from another machine does that person still see the error?
If another person attempts to access the workbook from the machine that could not, does that person see the error?

Watch MrExcel Video

Forum statistics

Latest member