Error When Open From Protected View

Marhier

Board Regular
Joined
Feb 21, 2017
Messages
128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I have managed to solve this problem solo. :cool:

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 [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
 
Upvote 0
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
 
Upvote 0
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:

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
 
Last edited:
Upvote 0
Would really appreciate some advice on this one.
Thank you.

Regards
Martin
 
Upvote 0
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
 
Upvote 0
Where did you add that code from SO?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top