PDA

View Full Version : [SOLVED] Protected View, Enable Edit causes Runtime Error 91 or 1004



MrRhodes2004
12-02-2016, 07:00 AM
Forum,

I have an xlsm file with macros that is shared in multiple methods including email, dropbox, google drive...

Because the file is opened after being downloaded, Excel opens it in "Protected View". When the user clicks "Enable Edit" a runtime error 91 or 1004 is thrown. I have tried to trap and handle the error without positive results. When the error is thrown, it pops up the typical error menu but since the file is still protected, I am not able to debug and find where in the code the problem is occurring.
I have tried multiple methods. The most recent attempt locked up excel. However, I think the method that is shown below only works when the file is opened from another macro enabled worksheet. This worksheet is only allowed to open when no other worksheets are open. This is done to reduce conflicts.

The workbook when closing switches to a sheet that is shown if macros_enable are not enabled and also protects the workbook. The macros_enable sheet shows how to change the options to allow macros. When the workbook is opened, if macros are enabled it switches to the correct sheet and hides the macro sheet.

The code snippet is below.



Private Sub Workbook_Open()
On Error Resume Next


ActiveWorkbook.Unprotect 'Since Macros are enabled, unprotect the workbook


'try to trap the enable edit error
Do While Application.ProtectedViewWindows.Count > 0
Application.ProtectedViewWindows(1).Edit
Loop


'complete previous actions
DoEvents

OnOpenActions 'calls code that performs actions required


The users are not very familiar with Excel. When the error is shown, they don't know what to do.

I am placing my code in ThisWorkbook on the open subroutine. Is this the proper place for such code? I am assuming that this is the first line of code that is read when a workbook is opened.

Problem 1) How to eliminate the runtime error when "enable edit" is clicked?
Problem 2) Is there a way, when the "enable button" is clicked, a msgbox is opened telling the user that the file will be saved and will be closed, close the file, and the user would then need to reopen the file?

Thanks for comments and thoughts.

MrRhodes2004
12-02-2016, 07:37 AM
Ok. So I have learned that the ActiveWorkbook.Unprotect throws the Runtime Error 91. I think Excel is getting ahead of itself. The file is not complete with the enable edit before it tries to run the unprotect. Will a DoEvents prior to the unprotect help?

Either way, I have the original two problems left to resolve.

Kenneth Hobs
12-02-2016, 07:52 AM
It would be best to probably zip your file and include instructions on how to remove the security. JKP has an example to do that. The link is not working right now. It involves right clicking the file in Explorer, Properties, Unblock. Unblock is only shown if it is blocked. That is so simple though a few lines of instructions should be sufficient.

e.g.
To unblock file after download:
1. Open Explorer (Win+E)
2. Browse to folder where file is at.
3. Right click it.
4. Select Properties.
5. Click Unblock button in lower right if shown.
6. Click OK button.
7. Open downloaded file.

MrRhodes2004
12-02-2016, 08:17 AM
Kenneth,

This works nicely and it will be included with the future versions of the file. However, I still need to deal with individuals who have no clue how to follow (or read) instructions. We all have been there. The instructions are perfect for those that do read and follow.

I have also noticed that when the error is thrown the protected view file is open in one window and the unprotected view is open in another window. So it clearly has not transitioned between the two states fully before it starts to run the code. Since the protected view is still active it tries to complete the code on that view and causes the error. Other than using Application.Wait, is there a way to make sure that the states have been fully switched before running code in the unprotected view?

Kenneth Hobs
12-02-2016, 08:27 AM
Yes, I have a golden seat there. Unfortunately, I have no automated solution. Microsoft can be sneaky or is it snarky?