Microsoft Office Tutorials and References
In Depth Information
Example 12-15. The IsBookOpen Function
Example 12-15. The IsBookOpen Function
Private Function IsBookOpen(sWkbName) As Boolean
' Check to see if workbook is open
' Note that an add-in workbook does not appear in
' the Workbooks collection, so we need another method.
' However, an add-in can be referenced by name, so we simply
' access its Name property. If an error occurs, then
' the workbook is not open.
Dim sName As String
On Error GoTo WkbNotOpen
IsBookOpen = True
sName = Workbooks(sWkbName).Name
Exit Function
WkbNotOpen:
IsBookOpen = False
End Function
12.9.4 Closing Any Open Add-Ins
When the user unchecks the SRXUtils item in the Add-Ins dialog, Excel will close the
SRXUtils.xla workbook. But it will not close any add-ins, such as Print.utl , that were opened in
code. The place to close all open add-ins is in the workbook's BeforeClose event, which currently
only deletes the custom menu.
A simple (but perhaps not elegant) approach is to close every add-in listed in the DataSheet except
the main SRXUtils.xla (which is closed when the user deselects the add-in). For this, we need an
On Error Resume Next line so that an attempt to close a workbook that is not open will be
ignored. Thus, you should change the code for the existing BeforeClose event to that shown in
Example 12-16 .
Example 12-16. The Workbook_BeforeClose Event Handler
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete custom menu and close all add-ins
Dim r As Integer
Dim ws As Worksheet
Dim sName As String
' In case we try to close a workbook that is not open
On Error Resume Next
DeleteCustomMenus
Set ws = ThisWorkbook.Worksheets("DataSheet")
For r = 2 To Application.WorksheetFunction.CountA(ws.Range("A:A"))
sName = ws.Cells(r, InWorkbook_Col).Value
If sName <> "" And sName <> "ThisWorkbook" Then
Workbooks(sName).Close
End If
Next r
End Sub
 
 
Search JabSto ::




Custom Search