I have an addin I created that uses an event class that
fires before close event of any book at to catch when a
book is being closed and prompts me via msgbox to ask if I
want to add any reminder notes (docuemntation)to the file.
If yes then I add text to cells on a specific sheet as
needed. I use this to track file modfications during
design, debug and ongoing use.

My issue is that while I have this as an addin myself, I
sometimes put the same code is specific workbooks that
others use soe that they can document/add notes as they
use books that are shared from time to time on the server.

As a result for these books I get the msgbox firing twice--
once on book close caught by the addin and once by the
book close event code of the book itself.

I'm looking for suggestions on how to have the addin
ignore the close if the book also has the code.

Option I've considered: Putting a dummy true/false value
in some specific cell of the book with the duplcate code,
and testing the value thru the addin.(problem if the sheet
gets deleted accidently)

Are there any better approaches you can recommend? ie any
properties I could test?

Dave

Dave

If there is a BeforeClose event do you want to prevent the add-in from
running, or is it only a specific BeforeClose event? How reliable is the
text in the individual workbooks' before close events? Here's something to
try: Put this function in your add-in project

Function HasBeforeClose(wbTarget As Workbook, _
Optional sUniqueText As String) As Boolean

Dim exProj As Object
Dim exModule As Object
Dim lProcStart As Long
Dim lProcCount As Long
Dim i As Long

Const sMODNM As String = "ThisWorkbook"
Const sPROCNM As String = "Workbook_BeforeClose"
Const vbext_pk_Proc As Long = 0

Set exProj = wbTarget.VBProject
Set exModule = exProj.VBComponents(sMODNM).CodeModule

On Error Resume Next
lProcStart = exModule.ProcBodyLine(sPROCNM, vbext_pk_Proc)
lProcCount = exModule.ProcCountLines(sPROCNM, vbext_pk_Proc)
On Error GoTo 0

If lProcStart = 0 Then
HasBeforeClose = False
Else
If InStr(1, exModule.Lines(lProcStart, lProcCount), sUniqueText) 0
Then
HasBeforeClose = True
End If
End If

End Function

Then in the add-in's BeforeClose you can do something like

Private Sub myapp_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)

If Not HasBeforeClose(Wb, "Would you like to add comments?") Then
'do your stuff
End If

End Sub

The text "Would you like to add comments?" is some unique text in the
individual workbooks' beforeclose code that will tell you if it has the
procedure that you would be duplicating. If you only need to determine that
the workbook has a BeforeClose (and not some specific code therein) then
don't supply the sUnique argument.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dave" wrote in message
...
I have an addin I created that uses an event class that
fires before close event of any book at to catch when a
book is being closed and prompts me via msgbox to ask if I
want to add any reminder notes (docuemntation)to the file.
If yes then I add text to cells on a specific sheet as
needed. I use this to track file modfications during
design, debug and ongoing use.

My issue is that while I have this as an addin myself, I
sometimes put the same code is specific workbooks that
others use soe that they can document/add notes as they
use books that are shared from time to time on the server.

As a result for these books I get the msgbox firing twice--
once on book close caught by the addin and once by the
book close event code of the book itself.

I'm looking for suggestions on how to have the addin
ignore the close if the book also has the code.

Option I've considered: Putting a dummy true/false value
in some specific cell of the book with the duplcate code,
and testing the value thru the addin.(problem if the sheet
gets deleted accidently)

Are there any better approaches you can recommend? ie any
properties I could test?

Dave

Maybe you could use a Workbook Name that you add in each workbook:

Option Explicit
Sub auto_open()
ThisWorkbook.Names.Add Name:="HasOwnCode", RefersTo:="True", Visible:=False
End Sub


Then in your application event code, you can check for it.

Option Explicit
Sub testme01()

Dim testVal As Boolean

testVal = False
On Error Resume Next
testVal = Evaluate(ActiveWorkbook.Names("HasOwnCode").Refers To)
On Error GoTo 0

If testVal = False Then
MsgBox "continue with application event"
Else
MsgBox "don't continue"
End If

End Sub


Dave wrote:

I have an addin I created that uses an event class that
fires before close event of any book at to catch when a
book is being closed and prompts me via msgbox to ask if I
want to add any reminder notes (docuemntation)to the file.
If yes then I add text to cells on a specific sheet as
needed. I use this to track file modfications during
design, debug and ongoing use.

My issue is that while I have this as an addin myself, I
sometimes put the same code is specific workbooks that
others use soe that they can document/add notes as they
use books that are shared from time to time on the server.

As a result for these books I get the msgbox firing twice--
once on book close caught by the addin and once by the
book close event code of the book itself.

I'm looking for suggestions on how to have the addin
ignore the close if the book also has the code.

Option I've considered: Putting a dummy true/false value
in some specific cell of the book with the duplcate code,
and testing the value thru the addin.(problem if the sheet
gets deleted accidently)

Are there any better approaches you can recommend? ie any
properties I could test?

Dave

--

Dave Peterson

Thanks Dick, your approach looks great, I'll give it a try

Dave


"Dick Kusleika" wrote in message
...
Dave

If there is a BeforeClose event do you want to prevent the add-in from
running, or is it only a specific BeforeClose event? How reliable is the
text in the individual workbooks' before close events? Here's something
to
try: Put this function in your add-in project

Function HasBeforeClose(wbTarget As Workbook, _
Optional sUniqueText As String) As Boolean

Dim exProj As Object
Dim exModule As Object
Dim lProcStart As Long
Dim lProcCount As Long
Dim i As Long

Const sMODNM As String = "ThisWorkbook"
Const sPROCNM As String = "Workbook_BeforeClose"
Const vbext_pk_Proc As Long = 0

Set exProj = wbTarget.VBProject
Set exModule = exProj.VBComponents(sMODNM).CodeModule

On Error Resume Next
lProcStart = exModule.ProcBodyLine(sPROCNM, vbext_pk_Proc)
lProcCount = exModule.ProcCountLines(sPROCNM, vbext_pk_Proc)
On Error GoTo 0

If lProcStart = 0 Then
HasBeforeClose = False
Else
If InStr(1, exModule.Lines(lProcStart, lProcCount), sUniqueText)
0
Then
HasBeforeClose = True
End If
End If

End Function

Then in the add-in's BeforeClose you can do something like

Private Sub myapp_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)

If Not HasBeforeClose(Wb, "Would you like to add comments?") Then
'do your stuff
End If

End Sub

The text "Would you like to add comments?" is some unique text in the
individual workbooks' beforeclose code that will tell you if it has the
procedure that you would be duplicating. If you only need to determine
that
the workbook has a BeforeClose (and not some specific code therein) then
don't supply the sUnique argument.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dave" wrote in message
...
I have an addin I created that uses an event class that
fires before close event of any book at to catch when a
book is being closed and prompts me via msgbox to ask if I
want to add any reminder notes (docuemntation)to the file.
If yes then I add text to cells on a specific sheet as
needed. I use this to track file modfications during
design, debug and ongoing use.

My issue is that while I have this as an addin myself, I
sometimes put the same code is specific workbooks that
others use soe that they can document/add notes as they
use books that are shared from time to time on the server.

As a result for these books I get the msgbox firing twice--
once on book close caught by the addin and once by the
book close event code of the book itself.

I'm looking for suggestions on how to have the addin
ignore the close if the book also has the code.

Option I've considered: Putting a dummy true/false value
in some specific cell of the book with the duplcate code,
and testing the value thru the addin.(problem if the sheet
gets deleted accidently)

Are there any better approaches you can recommend? ie any
properties I could test?

Dave

If you're using xl2002 or higher, there's a user setting that may foil Dick's
code.

Tools|Macro|Security|Trusted Sources Tab

If the user has not enabled "trust access to visual basic project", you might
have trouble.



DH wrote:

Thanks Dick, your approach looks great, I'll give it a try

Dave

"Dick Kusleika" wrote in message
...
Dave

If there is a BeforeClose event do you want to prevent the add-in from
running, or is it only a specific BeforeClose event? How reliable is the
text in the individual workbooks' before close events? Here's something
to
try: Put this function in your add-in project

Function HasBeforeClose(wbTarget As Workbook, _
Optional sUniqueText As String) As Boolean

Dim exProj As Object
Dim exModule As Object
Dim lProcStart As Long
Dim lProcCount As Long
Dim i As Long

Const sMODNM As String = "ThisWorkbook"
Const sPROCNM As String = "Workbook_BeforeClose"
Const vbext_pk_Proc As Long = 0

Set exProj = wbTarget.VBProject
Set exModule = exProj.VBComponents(sMODNM).CodeModule

On Error Resume Next
lProcStart = exModule.ProcBodyLine(sPROCNM, vbext_pk_Proc)
lProcCount = exModule.ProcCountLines(sPROCNM, vbext_pk_Proc)
On Error GoTo 0

If lProcStart = 0 Then
HasBeforeClose = False
Else
If InStr(1, exModule.Lines(lProcStart, lProcCount), sUniqueText)
0
Then
HasBeforeClose = True
End If
End If

End Function

Then in the add-in's BeforeClose you can do something like

Private Sub myapp_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)

If Not HasBeforeClose(Wb, "Would you like to add comments?") Then
'do your stuff
End If

End Sub

The text "Would you like to add comments?" is some unique text in the
individual workbooks' beforeclose code that will tell you if it has the
procedure that you would be duplicating. If you only need to determine
that
the workbook has a BeforeClose (and not some specific code therein) then
don't supply the sUnique argument.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dave" wrote in message
...
I have an addin I created that uses an event class that
fires before close event of any book at to catch when a
book is being closed and prompts me via msgbox to ask if I
want to add any reminder notes (docuemntation)to the file.
If yes then I add text to cells on a specific sheet as
needed. I use this to track file modfications during
design, debug and ongoing use.

My issue is that while I have this as an addin myself, I
sometimes put the same code is specific workbooks that
others use soe that they can document/add notes as they
use books that are shared from time to time on the server.

As a result for these books I get the msgbox firing twice--
once on book close caught by the addin and once by the
book close event code of the book itself.

I'm looking for suggestions on how to have the addin
ignore the close if the book also has the code.

Option I've considered: Putting a dummy true/false value
in some specific cell of the book with the duplcate code,
and testing the value thru the addin.(problem if the sheet
gets deleted accidently)

Are there any better approaches you can recommend? ie any
properties I could test?

Dave





--

Dave Peterson