I have a code that did work but then stopped for some users and now, after some time, also for me most of the time. It does not work when automatically run when opening excel, but it does work when I run it myself. I receive error 1004. Macro is placed in ThisWorkbook module. Please help to find the reason for the issue.

Code is supposed to remove password from workbook and unhide two tabs once it verifies that it is one of the users listed in tab Access. Debugger stops at:

LastRow = Worksheets("Access").Range("A:A").Cells(Rows.Count, 1).End(xlUp).Row

Full code:

Private Sub Workbook_Open()

Dim UserName As String
Dim myArray As Variant
Dim i As Long
Dim LastRow As Long

UserName = LCase(Environ$("username"))
LastRow = Worksheets("Access").Range("A:A").Cells(Rows.Count, 1).End(xlUp).Row
myArray = Worksheets("Access").Range("A1:A" & LastRow)
ReDim myArray(1 To LastRow) As Integer
    
    For i = LBound(myArray) To UBound(myArray)
        If LCase(Worksheets("Access").Range("A" & i).Value) = UserName Then
            ActiveWorkbook.Unprotect "Pswd1"
            Sheets("Reward").Visible = True
            Sheets("Pivot").Visible = True
            GoTo TUTAJ
        End If
    Next i
    
TUTAJ:
    
End Sub

As it might matter, there is another code running in that file BeforeSave, that hides two tabs previously unhidden and adds paswword on workoob. It works fine:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim UserName As String
Dim myArray As Variant
Dim i As Long
Dim LastRow As Long

UserName = LCase(Environ$("username"))
LastRow = Worksheets("Access").Range("A:A").Cells(Rows.Count, 1).End(xlUp).Row
myArray = Worksheets("Access").Range("A1:A" & LastRow)
ReDim myArray(1 To LastRow) As Integer
    
On Error GoTo WYJSCIE
    
    For i = LBound(myArray) To UBound(myArray)
        If LCase(Worksheets("Access").Range("A" & i).Value) = UserName Then
            Sheets("Reward").Visible = False
            Sheets("Pivot").Visible = False
            ActiveWorkbook.Protect Password:="Pswd1", Structure:=True, Windows:=False
            GoTo TUTAJ
        End If
    Next i
    
WYJSCIE:
TUTAJ:

End Sub

Tried to :

• remove ReDim myArray(1 To LastRow) As Integer and adding .value after myArray = Worksheets(“Access”).Range(“A1:A” & LastRow)
• enabling all macros without asking in excel options
• delaying this macro by adding:
  Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue(“00:00:01”), “ThisWorkbook.RunMacro”
  End Sub
  and renaming my main macro to RunMacro. In this scenario debugger stopped at this line
• making macros public
• removing code, saving file and pressing Debug -> CompileVBA Project, saving and reopening file
• running file from shared drive as well as from local drive
• going through internet and copilot for 8 hours looking for solution