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.
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.