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:
w.Edit 'Causes error 1004
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.