Excel 2016 VBA Unprotect error for single user

Deigs

New Member
Joined
Aug 12, 2020
Messages
1
Office Version
  1. 2016
Platform
  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
Worksheets("Log").Activate
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
Worksheets("Landing").Activate
Application.ScreenUpdating = True
Call Auto_Save
End Sub
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
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

Threads
1,114,019
Messages
5,545,531
Members
410,689
Latest member
ConfuzzledThomas
Top