Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Error When Open From Protected View

  1. #1
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Error When Open From Protected View

    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:
    Code:
    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

  2. #2
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    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:

    Code:
    Private Sub Workbook_Activate()
    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

  3. #3
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    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

  4. #4
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

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

    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

    Any advice would be greatly appreciated.
    Thank you.
    Regards
    Martin
    Last edited by Marhier; Nov 15th, 2017 at 03:31 AM.

  5. #5
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

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

    Regards
    Martin

  6. #6
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    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

  7. #7
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    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

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,841
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Error When Open From Protected View

    Where did you add that code from SO?

  9. #9
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    I kept it in 'ThisWork' book.

  10. #10
    Board Regular
    Join Date
    Feb 2017
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error When Open From Protected View

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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