FWIW
The approach I use doesn't involve Workbook events. (In fact I NEVER
use code in the 'ThisWorkbook' component to avoid problems of code not
working if the workbook becomes corrupt!) Saves are done normally as
per user normal work practice. Here's some sample code...
In a standard module named "m_OpenClose":
Option Explicit
' This module handles all startup/shutdown processes
'[Global Variable Defs]
Const gbDevMode As Boolean = False 'True'
'//set True to skip runtime UI settings during design time
Public gbSettingsAreStored As Boolean
'[Module Variable Defs]
Const msModule$ = "m_OpenClose" '//used for logging
'[Enums]
'[UDTs]
Private Type udtSettings
SettingsAreStored As Boolean
VisibleCommandbars As String
DisplayStatusBar As Boolean
DisplayFormulaBar As Boolean
ShowWindowsInTaskbar As Boolean
IgnoreRemoteRequests As Boolean
DisplayCommentIndicator As Boolean
'With Commandbars
CustomizeToolbars As Boolean
ViewToolbarList As Boolean
EnableDesktop As Boolean
'With editing
Calculation As Long
EditDirectlyInCell As Boolean
AlertBeforeOverwriting As Boolean
CellDragAndDrop As Boolean
CopyObjectsWithCells As Boolean
MoveAfterReturn As Boolean
MoveAfterReturnDirection As Long
'Version 10+
AutoRecoverEnabled As Boolean
'With Commandbars
DisableAskAQuestionDropdown As Boolean
'With ErrorCheckingOptions
InconsistentFormula As Boolean
UnlockedFormulaCells As Boolean
NumberAsText As Boolean
End Type
Public AppSettings As udtSettings
'[APIs]
'[Conditionals]
Sub Auto_Open()
' An Excel Autorun macro to replace the Workbook_Open event.
' Handles all startup processes.
Const sSource$ = "Auto_Open()"
'On startup...
InitGlobals '//initialize global variables
'If <startup condition Then Startup Else Shutdown
End Sub 'Auto_Open
Sub Startup()
Const sSource$ = "Startup()"
StoreExcelSettings '//loads user default settings into a UDT
SetupUI '//configures Excel as desired
CreateMenus '//sets up any custom menus/toolbars used
End Sub 'Startup
Sub Auto_Close()
' An Excel Autorun macro to replace the Workbook_BeforeClose event.
' Handles all shutdown processes.
Const sSource$ = "Auto_Close()"
'On shutdown conditionals
'Prompt to notify other users of changes
Dim vAns
Const sMsg$ = "Do you want to Notify others of your changes?"
vAns = MsgBox(sMsg, vbYesNo+vbQuestion, "Notification of Changes")
If vAns = vbYes Then NotifyOtherUsers
Call Shutdown
End Sub 'Auto_Close
Sub Shutdown()
Const sSource$ = "Shutdown()"
DeleteMenus '//remove any custom menus/toolbars created
CleanupUI '//undo changes made by SetupUI
RestoreExcelSettings '//reset stored user default settings
End Sub 'Shutdown
Sub NotifyOtherUsers()
' This is where you put code to notify others of changes
Const sSource$ = "NotifyOtherUsers()"
'...
End Sub 'NotifyOtherUsers
Sub StoreExcelSettings()
' This stores Excel's settings on startup
Const sSource As String = "StoreExcelSettings()"
Dim oTemp As Object
Dim wkbTemp As Object 'gAppXL.Workbook
Dim sBarNames As String
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If gAppXL Is Nothing Then Set gAppXL = Application '//vba only
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
'Skip errors in case we can't use the Registry
' On Error Resume Next
'Check if we've already stored the settings
'(so don't want to overwrite them)
' If Not gbSettingsAreStored Then
'Store the current Excel settings in AppSettings
With gAppXL
'Some properties require a workbook to be open
If .ActiveWorkbook Is Nothing Then .ScreenUpdating = False: Set
wkbTemp = .Workbooks.Add
AppSettings.DisplayStatusBar = .DisplayStatusBar
AppSettings.DisplayFormulaBar = .DisplayFormulaBar
AppSettings.DisplayCommentIndicator = .DisplayCommentIndicator
AppSettings.CustomizeToolbars =
..CommandBars("Tools").Controls("Customize...").En abled
AppSettings.ViewToolbarList = .CommandBars("Toolbar
List").Enabled
AppSettings.EnableDesktop = .CommandBars("Desktop").Enabled
AppSettings.IgnoreRemoteRequests = .IgnoreRemoteRequests
AppSettings.Calculation = .Calculation
AppSettings.AlertBeforeOverwriting = .AlertBeforeOverwriting
AppSettings.CellDragAndDrop = .CellDragAndDrop
AppSettings.CopyObjectsWithCells = .CopyObjectsWithCells
AppSettings.EditDirectlyInCell = .EditDirectlyInCell
AppSettings.MoveAfterReturn = .MoveAfterReturn
AppSettings.MoveAfterReturnDirection = .MoveAfterReturnDirection
'insert others here as necessary
'Which commandbars are visible
For Each cmdBar In .CommandBars
If cmdBar.Visible Then
sBarNames = sBarNames & "," & cmdBar.name
End If
Next
AppSettings.VisibleCommandbars = Mid$(sBarNames, 2)
'Specific for Excel 2000 and up
If Val(.VERSION) = 9 Then AppSettings.ShowWindowsInTaskbar =
..ShowWindowsInTaskbar
'Special for Excel 2002 and up
If Val(.VERSION) = 10 Then
Set oTemp = .CommandBars
AppSettings.DisableAskAQuestionDropdown =
oTemp.DisableAskAQuestionDropdown
AppSettings.AutoRecoverEnabled = .AutoRecover.Enabled
AppSettings.InconsistentFormula =
..ErrorCheckingOptions.InconsistentFormula
AppSettings.UnlockedFormulaCells =
..ErrorCheckingOptions.UnlockedFormulaCells
AppSettings.NumberAsText = .ErrorCheckingOptions.NumberAsText
End If 'Val(.VERSION) = 10
AppSettings.SettingsAreStored = True
End With 'gAppXL
'Close up the temporary workbook
If Not wkbTemp Is Nothing Then wkbTemp.Close False
'Save the settings
' PutDataInFile App.Path & gsSEP_PATH & gsAPP_FILE_SETTINGS,
SettingsData '//vb6
PutDataInFile sAppPath & "\settings.dat", SettingsData '//vba
'Indicate that the settings have been stored.
gbSettingsAreStored = True
' End If 'gbSettingsAreStored
End Sub 'StoreExcelSettings()
Sub RestoreExcelSettings()
' Restores Excel setting we stored at startup
Const sSource As String = "RestoreExcelSettings()"
Dim oTemp As Object
Dim vRet As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If gAppXL Is Nothing Then Set gAppXL = Application '//vba only
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
'we're shutting down
' On Error Resume Next
'Restore the original Excel settings
With gAppXL
'reset the window caption
.Caption = .name
'reset the sheet tab shortcut menu
.CommandBars("Ply").Reset
.CommandBars("Window").Reset
'Restore the Excel settings
' If gbSettingsAreStored Then
.DisplayStatusBar = AppSettings.DisplayStatusBar
.DisplayFormulaBar = AppSettings.DisplayFormulaBar
.DisplayCommentIndicator = AppSettings.DisplayCommentIndicator
.CommandBars("Tools").Controls("Customize...").Ena bled =
AppSettings.CustomizeToolbars
.CommandBars("Toolbar List").Enabled =
AppSettings.ViewToolbarList
.CommandBars("Desktop").Enabled = AppSettings.EnableDesktop
.IgnoreRemoteRequests = AppSettings.IgnoreRemoteRequests
On Error Resume Next
.Calculation = AppSettings.Calculation
On Error GoTo 0
.AlertBeforeOverwriting = AppSettings.AlertBeforeOverwriting
.CellDragAndDrop = AppSettings.CellDragAndDrop
.CopyObjectsWithCells = AppSettings.CopyObjectsWithCells
.EditDirectlyInCell = AppSettings.EditDirectlyInCell
.MoveAfterReturn = AppSettings.MoveAfterReturn
.MoveAfterReturnDirection = AppSettings.MoveAfterReturnDirection
'insert others here as necessary
'Specific for Excel 2000 and up
If Val(.VERSION) = 9 Then .ShowWindowsInTaskbar =
AppSettings.ShowWindowsInTaskbar
'Specific for Excel 2002 and up
If Val(.VERSION) = 10 Then
Set oTemp = gAppXL.CommandBars
oTemp.DisableAskAQuestionDropdown =
AppSettings.DisableAskAQuestionDropdown
.AutoRecover.Enabled = AppSettings.AutoRecoverEnabled
With .ErrorCheckingOptions
.InconsistentFormula = AppSettings.InconsistentFormula
.UnlockedFormulaCells = AppSettings.UnlockedFormulaCells
.NumberAsText = AppSettings.NumberAsText
End With
End If 'Val(.VERSION) = 10
' End If 'gbSettingsAreStored
End With
'Open the 'toolbar.xlb' to prevent it from growing in size
'This will restore the default Excel menubar in the event of a crash
''' DO NOT USE IF MENUBAR HAS BEEN CUSTOMIZED '''
'//This application uses a menuitem on the Excel Menubar, so we won't
restore the default//
' RestoreMenus
'Show the correct toolbars
'//These were hidden by ConfigureWorkspace()//
On Error Resume Next
For Each mvBarName In Split(AppSettings.VisibleCommandbars, ",")
gAppXL.CommandBars(mvBarName).Visible = True
Next
On Error GoTo 0
'Once restored, reset our flag for next runtime
AppSettings.SettingsAreStored = False
gbSettingsAreStored = False
'Save the settings
' PutDataInFile App.Path & gsSEP_PATH & gsAPP_FILE_SETTINGS,
SettingsData '//vb6
PutDataInFile sAppPath & "\settings.dat", SettingsData '//vba
End Sub 'RestoreExcelSettings()
Note:
Saving setting to file is primarily for use with VB6 automation, but it
allows for settings recovery in the event of a crash.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus