PDA

View Full Version : Error When Open From Protected View



Marhier
11-19-2017, 11:57 PM
Good morning all.
Before I start, I posted about this on the Mr. Excel forum 13/11/17, but struggling to get a response:
https://www.mrexcel.com/forum/excel-questions/1031183-error-when-open-protected-view.html
(https://www.mrexcel.com/forum/excel-questions/1031183-error-when-open-protected-view.html)

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

The code works fine when opened up from 'My Documents'...
The issue I'm having is, everyone that uses it will be viewing a read only version 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, but it fails to bring up the message I want everyone to see when the document is opened.

My code 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

I know opening in protected view leads to a problem in calling for modules of a WorkbookOpen event.
I tried changing this to Private Sub Workbook_Activate(), but doing this 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/42646125/workbook-open-sheet-activate-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:

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...
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

I've logged a call with my IT team to add our internal website link to the global Microsoft Trust Centre Settings, which should prevent this from happening, but they closed the call saying you can't add links and that it isn't possible... I'm of a different view, but don't want to go back and forth with them over it.

Would really appreciate any support on this.
Thank you.
Regards
Martin

Marhier
11-23-2017, 05:19 AM
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