Protected View Issue (Delaying Code execution?)

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
571
Office Version
  1. 365
Platform
  1. Windows
Enable_Err.PNG


Been chasing this rabbit for years.
When my very heavy macro-ed workbook has a name change, Excel will prompt me to "Enable Editing" upon opening the workbook.
I will then get a debug of some variation of this:
debug.PNG


I will then end the routine, close the workbook without saving it. When I reopen it, it will operate correctly until it is renamed again. rinse and repeat.

For some time this would only occur on my pc. Since I have several macros activating when the workbook is opened, I chalked it up to a code execution hold up while I enabled the workbook.
My problem is that since my last code update, this has been effecting other users. As I said, after they initially open and close the workbook, it will run fine from then on.

I understand that I probably caused this problem. My question is if there is some methods of either halting the code until the content is enabled or some method ignore the errors the first time around. and no, I do not want to use "On Error Resume Next".

One last thing, I do not use "Save As" when renaming this workbook. We found that by doing this method it continually increased the size of the workbook. We rename it using Window Explorer.

Thank you for considering this matter. I defer to the wisdom from abroad!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
What happens when you click the Debug button?
 
Upvote 0
You could put an unprotection code in the Workbook_Open event ??
 
Upvote 0
In response to you MARK858. This particular time it stopped here.

Stopped.PNG


However, I have had it stop in many different parts of the code. If it was always the same spot I would try to rewrite the code. As I mentioned before, this code runs without a problem after closing and reopening.

As for your question Michael, are you suggesting that I unprotect the code or the workbook? The code is never locked down. The workbook is locked and unlocked at different times.

Thanks for the questions, I hope my answers help. If you need more just ask.
 
Upvote 0
Another question. Could anyone direct me to how to turn off this prompt?
Enable_Err.PNG


I don't like to change security settings, but if there is no way to fix the code to accommodate this delay, my next move would be to eliminate the prompt.
 
Upvote 0
Since I have had no takers since my last post, I began to wonder if this might be related to a MS update. We did have an update around the same time as this began to manifest. Has no one been experiencing this except our team? It would help to know if this is a localized issue or more systemic.
 
Upvote 0
As mentioned above this causes one of the typical debugs:
VBA Code:
    For Each w In Workbooks
        If w.Name <> "PERSONAL.XLSB" And w.Name <> Application.ActiveWorkbook.Name Then
            e = e + 1
        End If
    Next w
The "Application.ActiveWorkbook.Name" gives the error <Object variable or With block variable not set>
This line is run upon opening the workbook. Apparently, it hits this line of code before the workbook is set as an object. I do know that the "Enable Editing" dialog is connected to the problem.
Enable_Err.PNG


Is there a command to force this workbook object to load before it hits this line? Perhaps a "DoEvents" or something along those lines?
 
Upvote 0
Ok, so I believe I getting closer my answer. Instead of forcing the workbook to load before the code ran, I tried this:
VBA Code:
    Do Until ytr = Application.ActiveWorkbook.Name
        ytr = Application.ActiveWorkbook.Name
    Loop
I thought if I could hold up the code until the workbook/worksheets load then I could avoid the crash.
However, I am still faced with the same problem unless I use "On Error Resume Next". Unfortunately, this will just put it into a perpetual loop.

It appears that the workbook will never load until I totally exit from the code.
 
Upvote 0
Well, since there has not been an overwhelming barrage of answers for this post. I must conclude that it has not affected many people or there is no hope of an answer or I need more deodorant :)

In any case, while I would like to resolve this problem, I am forced to do one of the following:
1. On error exit the worksheet, forcing them to reopen it without the errors.
2. Create a "Trusted Location" in Excel where they can open the workbook without the Protected View dialog popping up.

I don't consider this post closed, but I have spent enough effort trying to solve it without success.

Cheers.
 
Upvote 0
The protected view message is a security message to warn of a potential danger and as such is designed so it can't be hidden. So a bit like the Macro warning you have a make a user decision on what action to take i.e. either turn protected view off in the security settings in advance or click the enable editing button.
It would be a concern if you could easily bypass it by a code someone else had written.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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