Consulting

Results 1 to 2 of 2

Thread: Error When Open From Protected View

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    27
    Location

    Question Error When Open From Protected View

    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


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

  2. #2
    VBAX Regular
    Joined
    Oct 2017
    Posts
    27
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •