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]

That code really needs to be in a different startup workbook, usually an add-in. You also need some code in its Workbook_Open event to actually initialise the oApp variable.

 

[Marhier]

Thanks Rory, though this seems to be a little out of my comfort zone.

My IT Support team closed a call I logged about this, saying the following:

"After some investigation, excel doesn't allow to add internet locations or weblinks as trusted locations.
The only other option would be to disable protected view at all on all excels, which is not the safest thing to do.
We ran out of options".

I'm of an opinion you can add locations to the MS Office trusted locations, but I'm not 100% sure.
Would you say the reason they've given to close the call to be accurate?

Do you have any other suggestions how I could get my code to run when opening from protected view?

Appreciate it.
Thank you.
Regards
Martin

 

[RoryA]

Have you tried simply removing the Activate line from the original code, if that's the one causing the error? Making the sheet visible should activate it anyway.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
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
End Sub

I suspect they are correct about web links - you can add network locations to trusted locations, but I don't think web links work. You can disable protected view for files opened from internet locations but, as they say, that may not be wise.

 

[Marhier]

Thanks Rory... Your suggestion seems to have bypassed the issue I was having, but in doing this, it's given me a slightly different issue.

So to test your suggestion above, I uploaded my document to our external website and opened form there, but when opening, my screen flickered very quickly 50 or so times, which I assumed would be where it was making sure all the other sheets in my workbook are xlSheetVeryHidden... Though I would have thought having the Application.ScreenUpdating code in there would stop this?

I'd also like to point out, I test this on a few other user's computers and they half of them had the same issue I had, the other half didn't and it worked perfectly fine.

Any suggestions?

Thanks again, Rory.
Really appreciate all the help you've given me recently.

Regards
Martin

 

[RoryA]

Are you all using the same version of Excel?

 

[Marhier]

All of the users I tested it on originally, including myself are on Excel 2013.
The issue happened on some, not on others.
:S

I just tested it on an Excel 2010 user - the issue didn't occur.
I also tested it by logging onto our Citrix server, which has Excel 2010 and the issue didn't occur either.

Regards
Martin

 

[RoryA]

As far as I know, there are some differences with how Excel 2013 (and 2016) do screen updating - it's not as frequent - but I would expect all machines with the same version to behave the same (assuming the same build numbers). Does the same thing happen if you start Excel in Safe Mode?

 

[Marhier]

I can confirm the issue does not occur when opened in safe mode.

So the computers I tested it on, including myself, I know these users' trust centre settings are configured the same as mine, as I set it up for them a while ago... Probably why they are having the same issue as me.
The users I tested it on where the issue didn't occur, I hadn't touched.

 

[RoryA]

If it doesn't happen in Safe Mode, then it's most likely a startup file that is causing the problem. Try disabling all the installed add-ins and then test again. If that's OK, add them back one by one until the problem manifests again.

 

[Marhier]

The only add ins I had active were 'ASAP Utilities' and 'Acrobat PDFMaker Office COM Admin'.
I made sure everything was disabled, but the issue still occurs when opening from protected view.

Regards
Martin