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

 

[RoryA]

Try removing any startup files in either of your XLSTART folders and see if that helps.

 

[Marhier]

There was no file location in the field under Settings | Advanced | General.

Where are the XLSTART folders located for Excel 13?

I've looked here, but couldn't find anything:
C:\Program Files\Microsoft Office\Office15

Regards
Martin

 

[Marhier]

I found it.
I typed ?Application.StartupPath in the Immediate field in VB... Gave me the following:

C:\Users\*my user name*\AppData\Roaming\Microsoft\Excel\XLSTART

This folder was empty.

Is there another location to check?

 

[RoryA]

Are you using 32 or 64 bit Office? If it's 32 bit, you need to look in Program Files (x86) rather than Program Files.

 

[Marhier]

C:\Program Files (x86)\Microsoft Office\Office15\XLSTART
C:\Users\*my user name*\AppData\Roaming\Microsoft\Excel\XLSTART

Both of these folders were empty.

 

[RoryA]

Do you have any .xlb files?

 

[Jaafar Tribak]

Can you try the ScreenUpdating API alternative and see if it works :

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const WM_SETREDRAW = &HB

Private Property Let API_ScreenUpdating(ByVal Update As Boolean)
    SendMessage Application.hwnd, WM_SETREDRAW, Update, 0
End Property


Private Sub Workbook_Open()

    Dim ws As Worksheet
    
    On Error GoTo errHandler
    
[B][COLOR=#0000ff]    API_ScreenUpdating = False[/COLOR][/B]
    
    With Home
        .Visible = xlSheetVisible
    End With
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Home.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
    
errHandler:
[COLOR=#0000ff][B]    API_ScreenUpdating = True[/B][/COLOR]
    If Err <> 0 Then Err.Raise Err.Number

End Sub

 

[Jaafar Tribak]

I've noticed that the above code doesn't update the screen tabs after it is executed so try the following code instead :

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function InvalidateRect Lib "user32" (ByVal hwnd As LongPtr, ByVal lpRect As Long, ByVal bErase As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function InvalidateRect Lib "user32" (ByVal hwnd As Long, ByVal lpRect As Long, ByVal bErase As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const WM_SETREDRAW = &HB

Private Property Let API_ScreenUpdating(ByVal Update As Boolean)
    SendMessage Application.hwnd, WM_SETREDRAW, Update, 0
    If Update Then Call InvalidateRect(0, 0, 0)
End Property


Private Sub Workbook_Open()

    Dim ws As Worksheet
    
    On Error GoTo errHandler
    
    [B][COLOR=#0000ff]API_ScreenUpdating = False[/COLOR][/B]
    
    With Home
        .Visible = xlSheetVisible
    End With
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Home.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"
         
errHandler:
    [COLOR=#0000ff][B]API_ScreenUpdating = True[/B][/COLOR]
    If Err <> 0 Then Err.Raise Err.Number

End Sub

 

[Marhier]

Hi Jaafar, and thank you for your help.
I tried the code you suggested above, but when opening from protected view, my screen still flickers 50 times, obviously setting all sheets in the workbook to xlSheetVeryHidden.

Any other suggestions?

Also, Rory.
No .xlb files.

Regards
Martin

 

[ZVI]

Have found this (old) thread in the Trending Topics list on the bottom of the forum's page.
May be this?

' Put all the below code into ThisWorkbook module
Option Explicit
 
Private Sub Workbook_Open()
  Application.OnTime Now, Me.CodeName & ".Workbook_Open_Delayed"
End Sub
 
Private Sub Workbook_Open_Delayed()
  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