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]

I have managed to solve this problem solo.

Turns out opening in protected view leads to a problem in calling for modules of a WorkbookOpen event.
Changing it to a WorkbookActivate event solved this issue:

My code is now as follows:

Private Sub [U][B]Workbook_Activate[/B][/U]()
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

 

[Marhier]

Good morning.
I thought I had fixed this error, but changing to Private Sub Workbook_Activate() gives me the issue of if I have more than one Excel sheet open, every time I swap between the two, this code runs, which isn't what I want.

So I'm still seeking help on this one.

Any advice would be greatly appreciated.
Thank you.
Regards
Martin

 

[Marhier]

Good morning.
I thought I had fixed this error, but changing to Private Sub Workbook_Activate() gives me the issue of if I have more than one Excel sheet open, every time I swap between sheets, the code runs, which isn't what I want.

After browsing the internet and stumbling across a post on stackoverflow:
https://stackoverflow.com/questions...e-error-in-protected-view-when-enable-editing

I've added the code suggested by user 'Peh', but nothing seems to happen when I open my workbook, both from my Desktop, or from the internet in protected view.
See the following code - I've bold where I've added my code:

Option Explicit

Public WithEvents oApp As Excel.Application
Private bDeferredOpen As Boolean

Private Sub oApp_WorkbookActivate(ByVal Wb As Workbook)
    If bDeferredOpen Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(Wb)
    End If
End Sub

Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
    Dim oProtectedViewWindow As ProtectedViewWindow
    On Error Resume Next
        'The below line will throw error (Subscript out of range) if the workbook is not opened in protected view.
        Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(Wb.Name)
    On Error GoTo 0 'Reset error handling

    If oProtectedViewWindow Is Nothing Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(Wb)
    Else
        'Delay open actions till the workbook gets activated.
        bDeferredOpen = True
    End If
End Sub

Private Sub WorkbookOpenHandler(ByVal Wb As Workbook)
  'The actual workbook open event handler code goes here...[B]
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[/B]

Any advice would be greatly appreciated.
Thank you.
Regards
Martin

 

[Marhier]

Would really appreciate some advice on this one.
Thank you.

Regards
Martin

 

[Marhier]

Good morning.
Still seeking advice on this one.
I'd like to note that I have also taken this to another forum and thought best to post the link to it here now:

http://www.vbaexpress.com/forum/showthread.php?61372-Error-When-Open-From-Protected-View

Regards
Martin

 

[Marhier]

Apologies to bump this, but was wondering if what I'm asking to achieve is actually doable?
Have I made a mistake in how I've used the code taken from stackoverflow?

Much appreciated.
Thank you.
Regards
Martin

 

[RoryA]

Where did you add that code from SO?

 

[Marhier]

I kept it in 'ThisWork' book.

 

[Marhier]

Should I be putting this code somewhere else; in a module perhaps?
I've tried, but to no avail.

I imagine it would need to stay in the 'ThisWorkbook'?

Appreciate any help mate.
Thank you.
Regards
Martin