I have two custom add-ins loaded that use application-level events. I only want those events to run when a workbook associated with my application is active. Otherwise, the events in my purchase order application will try and do stuff to my invoices and vice versa. Not good.
For every application, the first thing I do is test to make sure I'm dealing with an appropriate object; be it a sheet, workbook, or whatever. I use two utilities to do the testing. The first utility verifies an open workbook and the second verifies a closed one.
As described in Custom Document Properties, I use custom document properties in my templates to identify the workbook as being part of the application. The utilities check the property and return True if it's there.
Function IsOpenInvoice(ByRef Wb As Workbook, _
Optional ByVal sProperty As String = gsCDPINVAPP) As Boolean
Dim bTemp As Boolean
On Error Resume Next
bTemp = Wb.CustomDocumentProperties(sProperty).Value
On Error GoTo 0
IsOpenInvoice = bTemp
End Function
Function IsInvoice(ByVal sName As String, _
Optional ByVal sProperty As String = gsCDPINVAPP) As Boolean
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.Filename = Dir(sName)
If Not sName = Dir(sName) Then
.LookIn = Replace(sName, Dir(sName), "")
End If
.PropertyTests.Add sProperty, msoConditionIsYes
.Execute
IsInvoice = .FoundFiles.Count> 0
End With
End Function
In IsOpenInvoice, the property value from the supplied workbook is set to a Boolean variable. If the property value is False or if the property doesn't exist, the variable is False. Of course the property value will never be false. I created the properties in the template and set the value to True. It will either exist or not.
For closed workbooks, IsInvoice uses FileSearch to find a file with the proper name and with the correct property. The argument sName is the full path and name of the file. If the file exists in the directory and has the property, True is returned. This is typically used before a file is opened, such as after GetOpenFilename is used but before the file is actually opened.
Both functions have an optional second argument, sProperty, that defaults to the property name that identifies the application in general. I also use these utilities to identify particular templates within my app. I may need to know, for instance, if the open workbook is an invoice, a sales order, or a report in particular, rather than just part of my app in general. I have constants set up for each template type that I want to test.
Here's an example of an application level event in a custom class module that uses IsOpenInvoice:
Private Sub mApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsOpenInvoice(Sh.Parent, gsINVOICE) Then
'Do stuff
End If
End Sub
