Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.
'Auto_Open' macro error occurs after 'Enable Editing' clicked.
We use an Excel form (Excel 2010… xlsm) that is sent via our email system (Outlook 2010) for approval.
The authorizing person opens the spreadsheet from Outlook,
Approves or Disapproves the request form and emails it to the appropiate people.
This spreadsheet contains an 'Auto_Open' macro that must run when the file is opened
to determine the state of the request, etc.
When we upgraded recently to Office 2010, these forms started producing VB errors
AFTER clicking 'Enable Editing'.
Excel 'Trust Center' configuration:
Protected View= 'Enable Protect View for Outlook attachments' (default)
Macro Settings = 'Enable all macros . . .'
(The 'Macro Settings' vary from PC to PC, so the form must work in all configurations.)
The error occurs when the 'Auto_Open' macro trys to execute any statements that interact with the form itself.
All attempts to activate the workbook or sheet fail.
The status of the workbook after clicking 'Enable Editing' is 'ActiveWorkbook is Nothing' = TRUE.
All of the following statements cause an error:
Application.ActiveProtecte
ThisWorkbook.Activate 'Causes error 1004
ActiveWorkbook.Activate 'Causes error 91
ActiveSheet.Select 'Causes error 91
Range("A1").Select 'Causes error 1004
I attached a test file that demonstates the issue.
Any suggestions would be appreciated.
Auto-Open-Test-Wkbk.xlsm
The authorizing person opens the spreadsheet from Outlook,
Approves or Disapproves the request form and emails it to the appropiate people.
This spreadsheet contains an 'Auto_Open' macro that must run when the file is opened
to determine the state of the request, etc.
When we upgraded recently to Office 2010, these forms started producing VB errors
AFTER clicking 'Enable Editing'.
Excel 'Trust Center' configuration:
Protected View= 'Enable Protect View for Outlook attachments' (default)
Macro Settings = 'Enable all macros . . .'
(The 'Macro Settings' vary from PC to PC, so the form must work in all configurations.)
The error occurs when the 'Auto_Open' macro trys to execute any statements that interact with the form itself.
All attempts to activate the workbook or sheet fail.
The status of the workbook after clicking 'Enable Editing' is 'ActiveWorkbook is Nothing' = TRUE.
All of the following statements cause an error:
Application.ActiveProtecte
ThisWorkbook.Activate 'Causes error 1004
ActiveWorkbook.Activate 'Causes error 91
ActiveSheet.Select 'Causes error 91
Range("A1").Select 'Causes error 1004
I attached a test file that demonstates the issue.
Any suggestions would be appreciated.
Auto-Open-Test-Wkbk.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi, baldwin_it.
This is a known issue with Excel 2010 - please see here for an explanation and a workaround.
Regards,
Brian.
This is a known issue with Excel 2010 - please see here for an explanation and a workaround.
Regards,
Brian.
Thanks for the KB article 2745652.
I also found that I could also trap it at the 'Workbook_Open' & 'Workbook_Activate' event.
(ThisWorkbook' module)
Option Explicit
Dim WBprotected As Boolean
Private Sub Workbook_Open()
If Application.ProtectedViewW
WBprotected = True
End If
End Sub
Private Sub Workbook_Activate()
If WBprotected = True Then
Call Auto_Open 'Auto_Open will not automatically run
Else
'Auto_Open will automatically run in 'Module1'
End If
End Sub
I also found that I could also trap it at the 'Workbook_Open' & 'Workbook_Activate' event.
(ThisWorkbook' module)
Option Explicit
Dim WBprotected As Boolean
Private Sub Workbook_Open()
If Application.ProtectedViewW
WBprotected = True
End If
End Sub
Private Sub Workbook_Activate()
If WBprotected = True Then
Call Auto_Open 'Auto_Open will not automatically run
Else
'Auto_Open will automatically run in 'Module1'
End If
End Sub
Did you try the 'KB article 2745652' article VB routine?
I could not get it to even be triggered as an event.
I copied the ''2745652' routines into the 'ThisWorkbook' object and inserted some 'Stop' syntax so as to step through the event process. When opening the test spreadsheet from Outlook, it never stopped in any of the '2745652' code and produced the original error. The 'Stop' normally works in other event routines, so I assume it should for 'oApp_WorkbookOpen', 'oApp_WorkbookActivate' & 'WorkbookOpenHandler'.
Yesterday I was able to get the routines I posted above to work without the '2745652' routines, but today I am not, so I am back to square one. Any ideas as to what I am missing?
I could not get it to even be triggered as an event.
I copied the ''2745652' routines into the 'ThisWorkbook' object and inserted some 'Stop' syntax so as to step through the event process. When opening the test spreadsheet from Outlook, it never stopped in any of the '2745652' code and produced the original error. The 'Stop' normally works in other event routines, so I assume it should for 'oApp_WorkbookOpen', 'oApp_WorkbookActivate' & 'WorkbookOpenHandler'.
Yesterday I was able to get the routines I posted above to work without the '2745652' routines, but today I am not, so I am back to square one. Any ideas as to what I am missing?
baldwin_it,
My ISP is having problems so I'm getting on-line very intermittently. Hopefully, I'll get back to you later today.
Edit: On seconds thoughts, this has been going on for long enough already without my internet problems causing you further delays. The question was obviously closed too early but if you have it reopened now, very few people are going to notice it, so please hit the "Request Attention" button, ask for the question to be deleted and then immediately open a new one with the relevant bits from here.
Brian.
My ISP is having problems so I'm getting on-line very intermittently. Hopefully, I'll get back to you later today.
Edit: On seconds thoughts, this has been going on for long enough already without my internet problems causing you further delays. The question was obviously closed too early but if you have it reopened now, very few people are going to notice it, so please hit the "Request Attention" button, ask for the question to be deleted and then immediately open a new one with the relevant bits from here.
Brian.
I discovered today why my above routines were not working. In addition to having an 'Auto_Open' routine in my workbook, I have 'Auto_Close' & 'SelectionChange' routines which were getting triggered prematurely and causing an error.
Here is the event order assuming the following security settings:
'Disable All Macros w/Notification'
& 'Enable Protected View for Outlook Attachments'
'Enable Editing' alert bar
'Enable Content' alert bar
Workbook_Open()
SelectionChange()
Auto_Close()
Workbook_Activate()
Auto_Open()
When 'SelectionChange' or 'Auto_Close' routines are executed while transitioning from 'Protected View' an error will occur whenever the workbook object is referenced.
I modified the 'ThisWorkbook' routines as follows:
Private Sub Workbook_Open()
If Application.ProtectedViewW
WBprotected = True
Processing = True
End If
End Sub
Private Sub Workbook_Activate()
If WBprotected = True Then
Processing = False 'WkBk now enabled
Call Auto_Open 'Auto_Open will not automatically run, so force it to run.
Else
'Auto_Open will automatically run
End If
End Sub
I also added
If Processing = True Then Exit Sub
to the beginning of the 'SelectionChange()' and 'Auto_Close' routines.
This stopped those routines from executing and causing an error and solved my problem.
Thanks for the interaction, I appreciate it. Re-opening the Question is not necessary.
Here is the event order assuming the following security settings:
'Disable All Macros w/Notification'
& 'Enable Protected View for Outlook Attachments'
'Enable Editing' alert bar
'Enable Content' alert bar
Workbook_Open()
SelectionChange()
Auto_Close()
Workbook_Activate()
Auto_Open()
When 'SelectionChange' or 'Auto_Close' routines are executed while transitioning from 'Protected View' an error will occur whenever the workbook object is referenced.
I modified the 'ThisWorkbook' routines as follows:
Private Sub Workbook_Open()
If Application.ProtectedViewW
WBprotected = True
Processing = True
End If
End Sub
Private Sub Workbook_Activate()
If WBprotected = True Then
Processing = False 'WkBk now enabled
Call Auto_Open 'Auto_Open will not automatically run, so force it to run.
Else
'Auto_Open will automatically run
End If
End Sub
I also added
If Processing = True Then Exit Sub
to the beginning of the 'SelectionChange()' and 'Auto_Close' routines.
This stopped those routines from executing and causing an error and solved my problem.
Thanks for the interaction, I appreciate it. Re-opening the Question is not necessary.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial