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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,896
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,283
Messages
5,769,218
Members
425,530
Latest member
Workingstiff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top