Microsoft Office Tutorials and References
In Depth Information
Workbook_BeforeClose Event
installed, and if it isn’t, ask the users of your workbook if they would like you to install the Analysis
ToolPak add-ins for them.
This procedure uses a Yes/No message box to ask permission (which you should always do before
changing another person’s computer settings) to install add-ins, and if they click Yes, the Analysis
ToolPak add-ins are installed:
Private Sub Workbook_Open()
If Not AddIns(“Analysis ToolPak”).Installed = True Then
Dim myConfirmation As Integer
myConfirmation = _
MsgBox(“I notice the Analysis ToolPak add-ins are not installed.” & vbCrLf & _
“Would you like me to install them for you now?”, _
vbQuestion + vbYesNo, _
“Analysis ToolPak not installed”)
If myConfirmation = vbNo Then
MsgBox “The ToolPak add-ins were not installed.”, vbInformation, “You clicked No.”
Else
AddIns(“Analysis ToolPak”).Installed = True
AddIns(“Analysis ToolPak - VBA”).Installed = True
MsgBox “The ToolPak add-ins have been installed.”, _
vbInformation, _
“Thanks for confirming.”
End If
End If
End Sub
Workbook_Beforeclose Event
The Workbook_BeforeClose event is triggered just before the workbook closes. This event is often
used in conjunction with the Workbook_Open event, to set a workbook back to its original state if
the Workbook_Open event temporarily changed the user’s Excel settings.
The following example is one way to apply the Workbook_BeforeClose event’s versatility. You can
tell Excel to save your workbook automatically when you close it, to avoid Excel’s prompt that asks
you if you want to save your changes.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
Workbook_Activate Event
The Workbook_Activate event is triggered when the workbook is activated, such as when the
workbook is opened, or when you switch between that workbook and other open workbooks. In this
example, the following procedure maximizes the Excel window when you activate the workbook:
Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized
End Sub
Search JabSto ::




Custom Search