VBA Workbook Open doesn't run after Enable Content prompt

[gbrenkman]

I have a situation where I save a macro-enabled workbook to a untrusted folder (like my users will probably do). When I open the file from Excel (most recent list or File > Open), the file opens and I get the warning banner and click Enable Content. I have a "Hello World" msgBox at the top of the Workbook_Open routine, but the message (and hence the rest of the code) does not appear.
If I close and restart Excel, then open the file, the Workbook_Open event runs.
Any ideas how to solve this? Having users set up trusted locations on multiple folders they might be saving to is not a good solution, nor is requiring them to shutdown and restart Excel workable either.

 

[dhilipindia]

can you share the code and screenshot

 

[gbrenkman]

Here is the code for attached to a ActiveX button on the spreadsheet that prompts for file name and saves two versions of the file the user is working on: 1) a macro-enabled, editable file saved in .xlsm format, and 2) a "locked down" version were all cells are locked as a standard Excel .xlsx format:

Private Sub btn_CreateLockedFile_Click()


'Run when user clicks the Save and Created Locked File button.

On Error GoTo CreateLockedFileError

Application.EnableEvents = True 'Enable event trapping


worksheetPassword = "unlock"
workbookPassword = "unlock"


'Use the standard Save As dialog box to prompt the user for the file name.
'This doesn't actually save the file. We'll do that later.
'For the File Save As here, limit to Excel Macro-Enabled Workbooks since VB code is running now.

fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlxm")

If fileSaveName = False Then Exit Sub 'User clicked Cancel without providing a name.

'First, save the file "as is" with macros, formulas, locked/unlocked cells, all worksheets, etc.
'as a normal Excel macro-enabled file.

Application.ThisWorkbook.SaveAs Filename:=fileSaveName, AddToMRU:=True '(adds filename to most recently used list)

'Now, create a "locked down" version that is in Finalized format where all formulas
'have been replaced by values and lock all the cells.
'Only save the Quote Sheet, Quote Notes and Out of Warranty Policy worksheets.
'Append "-L" to the filename to indicate it's a locked file.

'Put into Finalize mode

Call ChangeMode("FINALIZE")

'Hide the Control Panel

Call ChangeStatus("SHOW HEADER") 'Keeps the header info, hides the panel itself, but not the control panel header

Application.EnableEvents = False 'Don't trigger any events
Range("rows_ControlPanelHeader").EntireRow.Hidden = True 'Hide the control panel header too.

'Hide the Insert Row(s) and Delete Row buttons above the grid

btn_InsertRows.Visible = False 'Hide the "Insert Row(s)" button
btn_DeleteRow.Visible = False 'Hide the "Delete Row" button


'On the "Quote Sheet" (Sheet1) worksheet, copy/paste values to get rid of formulas.

Sheet1.Activate
Sheet1.Unprotect Password:=worksheetPassword 'This variable is set on the workbookOpen event.
currentCellAddress = ActiveCell.Address 'Save the current cell address
Cells.Select 'Select all cells
Selection.Copy 'Cope then paste special values.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False 'Turn off dancing ants

'Protect all cells and password protect the sheet

Selection.Locked = True

ActiveSheet.Protect Password:=worksheetPassword, DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
ActiveSheet.EnableSelection = xlNoRestrictions 'This allows users to select cells

Range(currentCellAddress).Select 'Reselect the current cell

Application.DisplayAlerts = False 'Prevents the pop-up asking if user really wants to delete the following worksheet


ActiveWorkbook.Unprotect Password:=workbookPassword 'Unprotect the workbook so you can delete a worksheet

Sheet11.Delete 'Delete the "Instructions" (Sheet11) worksheet

ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=workbookPassword 'Re-protect the workbook



Sheet1.Select 'Select the Quote Sheet worksheet
Range("A1").Select 'Select the upper-left corner

Application.DisplayAlerts = True 'Turn on alerts

'Take the .xlsm (macro-enabled) extension off the fileSaveName
fileSaveName = Left(fileSaveName, Len(fileSaveName) - 5)

'Put the "-L" suffix on the file name
fileSaveName = fileSaveName & "-L"

'Save the file as a normal Excel (non-macro-enabled) file.

Application.DisplayAlerts = False 'Prevents the pop-up asking if user really wants to save without macros

Application.ThisWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlWorkbookDefault, AddToMRU:=True '(adds filename to most recently used list)

Application.DisplayAlerts = True 'Turn on alerts

'Close the locked file (no need to save changes, as just saved)

ThisWorkbook.Close SaveChanges:=False


Exit Sub

Here is the code on the ThisWorksheet Open action:

Private Sub Workbook_Open()
'Run whenever the workbook is opened.

On Error GoTo WorkbookOpenError

Application.Calculation = xlCalculationAutomatic 'Turn on auto calculation

Application.EnableEvents = True 'Turn on event triggers

MsgBox "Hello World"

'Store the user's current preference for how they want selection to move after pressing Enter key.
'We'll restore this upon closing the file.

currentMoveAfterReturnSetting = Application.MoveAfterReturn

'Change the selection behavior so that it sticks in the same cell after editing
Application.MoveAfterReturn = False 'Prevent the selection/cursor from moving position after user presses Enter key

'Store the user's current preference for error checking flags
'We'll restore this upon closing the file

currentUnlockedFormulaCellsSetting = Application.ErrorCheckingOptions.UnlockedFormulaCells

'Change the error checking setting
Application.ErrorCheckingOptions.UnlockedFormulaCells = False

'Turn on protection on the Quote Sheet worksheet (Sheet1)
'Cells that are locked cannot be changed. User cannot insert/delete rows/columns.
'User cannot manually hide/show rows/columns.
'They can change unlocked cells, and change formatting for cells, rows and columns.
'The changes only apply to the user interface, not to macros, so macros are free to work
'with all cells and do insert/delete/hide/show.

Sheet1.Protect Password:="unlock", UserInterfaceOnly:=True, Contents:=True, _
DrawingObjects:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
AllowDeletingColumns:=False, AllowDeletingRows:=False, AllowSorting:=False, _
AllowUsingPivotTables:=False

Exit Sub

WorkbookOpenError:

MsgBox "There was an error in the WorkbookOpen routine."
Exit Sub

End Sub

When the user opens the macro-enabled file created by the code on the ActiveX button, they see the normal Security Warning -- Some active content has been disabled. Click here for more details. [Enable Content] banner

When the user clicks the button, the Workbook_Open code does not run as the "Hello World" message does not appear, but if you close Excel and re-open the same file, after you click Enable Content, Workbook_Open does run with the message.

I should also mention that when Workbook_Open fails to run, event triggers are disabled and other events such Change and ChangeSelection actions on the Quote Sheet worksheet don't run either (but do after shutting down and restarting Excel).

 

[gbrenkman]

I believe I've solved the problem. I added Application.EnableEvents = TRUE before saving each version of the file and before the close action in the btn_CreateLockedFile_Click() routine. Apparently, if a file is saved with events disabled, events will remain disabled when the file is re-opened in the same Excel instance, at least in my case.