Error When Open From Protected View

[Marhier]

Good afternoon
I've written a bit of code that when my workbook is opened, it makes sure a particular sheet is visible and then activated/selected.
After it has done this, it makes sure all inactive sheets are marked as xlSheetVeryHidden.

The code works fine when opened up from 'My Documents'...
The issue I'm having is, all other users will be viewing a read only version, which is linked from an internal website.
Opening from there has Excel open in protected view... When users click 'Enable Content', they get the following error message:

Run-time error '1004':
Method 'Activate' of object'_Worksheet' failed


It doesn't stop my sheet from opening, and it seems to open at the correct page... Just it fails to bring up an important message box I want users to see when the document is opened.

My code in the 'ThisWorkbook' module and is as follows:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
With Home
    .Visible = xlSheetVisible
        .Activate
End With
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
MsgBox "Please do not save this tool locally. Always open from Nexus to make sure you're using the most up to date prices"
Application.ScreenUpdating = True
End Sub

If someone could provide a solution to this problem, it would be greatly appreciated.
Thank you.
Regards
Martin

 

[Marhier]

Hey ZVI and thank you for having a look.
I tried the code you suggested, but when opening from protected view, it gives me the following error:

Run-time error '1004'
Method 'OnTime' of object '_Application' failed

Kind regards
Martin

 

[cdh44]

Any solution to this?

 

[ZVI]

The code of the post 30 always works good on my side.
But theoretically it can depend on some factors.
Try this modification of that code:

' Put all the below code into ThisWorkbook module
Option Explicit
 
Private Sub Workbook_Open()
  Application.CalculateUntilAsyncQueriesDone  ' <-- added
  Application.OnTime Now, Me.CodeName & ".Workbook_Open_Delayed"
End Sub
 
Private Sub Workbook_Open_Delayed()
  Application.CalculateUntilAsyncQueriesDone  ' <-- added (here is just for the testing)
  Application.ScreenUpdating = False
  Dim ws As Worksheet
  With Home
    .Visible = xlSheetVisible
    .Select
  End With
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
      ws.Visible = xlSheetVeryHidden
    End If
  Next ws
  MsgBox "Please do not save this tool locally. Always open from Nexus to make sure you're using the most up to date prices"
  Application.ScreenUpdating = True
End Sub

 

[ZVI]

There is also another approach:

' Put all the below code into ThisWorkbook module
Option Explicit
 
Dim IsInOpening As Boolean
 
Private Sub Workbook_Open()
  IsInOpening = True
End Sub
 
Private Sub Workbook_Activate()
  If IsInOpening Then
    IsInOpening = False
    If Val(Application.Version) >= 14 Then Application.CalculateUntilAsyncQueriesDone
    Application.OnTime Now, "'" & Me.FullName & "'!" & Me.CodeName & ".Workbook_Open_Delayed"
  End If
End Sub
 
Private Sub Workbook_Open_Delayed()
  ' Your code is here
  Application.ScreenUpdating = False
  Dim ws As Worksheet
  With Home
    .Visible = xlSheetVisible
    .Select
  End With
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
      ws.Visible = xlSheetVeryHidden
    End If
  Next
  MsgBox "Please do not save this tool locally. Always open from Nexus to make sure you're using the most up to date prices"
  Application.ScreenUpdating = True
End Sub