"noname" wrote in message
...
On Nov 19, 12:13 am, noname wrote:
On Nov 18, 4:08 pm, "Peter T" <peter_t@discussions wrote:

The simplest way is as Malik suggested, though you need to change the
Cancel
argument to True if you want to abort the close.

You can also control Close of some or all workbooks from within your
workbook1, eg all workboooks whose name starts with [say] "myData".

In workbook1 add a class module and rename it to clsAppEvents, and the
following in the class and a normal module as indicated

'' code in class named clsAppEvents

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Const cNAME As String = "book2" ' << CHANGE

If Left$(LCase$(Wb.Name), Len(cNAME)) = cNAME Then
If MsgBox("Sure you want to close " & Wb.Name & " ?", _
vbYesNo Or vbQuestion) < vbYes Then
Cancel = True
End If
End If

End Sub

''' end class clsAppEvents

''' code in a normal module
Private clsApp As clsAppEvents

Sub StartAppEvents()
' call from say thisworkbook's open event

Set clsApp = New clsAppEvents
Set clsApp.xlApp = Application

End Sub

''' end normal module

Run StartAppEvents to start trapping application level events, such as
workbook close events

Regards,
Peter T

"noname" wrote in message

...

Hi,

I have 2 workbooks viz workbook1 and workbook2.

I have code running ONLY in workbook1 and workbook2 is just any
workbook opened by workbook1, which contains data on which the
workbook1 works on.

At any instance, if i close workbook2 from the Close "X" button or
using the file close menu, the code in workbook1 should be able to
detect the change and STOP workbook2 from closing and instead present
a "CLOSE WORKBOOK2 - Yes/No" message box.

I need some kind of API or handle which will detect/grab the closing
of workbook2 and present the message box.

Is this possible?

---------------------------------------------------------------------

Hi Peter.

Your code doesn't seem to work. Only once (first run) it seemed to
trigger, but gave an error on "cNAME" with error message "You need to
define a constant."

On subsequent runs, when i try to close the Book2.xls file, it closes
without giving message box.

Is there some problem with WithEvents ????

-----------------------------------------------------------------------

Ok. I mistakenly had changed the "book2" to "Book2" and overlooked the
LCase function.

Works now.

My Changes:
------------------
In ThisWorkbook:

Private Sub Workbook_Open()
StartAppEvents
End Sub


In Module1:
----------------

Public clsApp As clsAppEvents
Public sName As String

Sub StartAppEvents()
' call from say thisworkbook's open event

Set clsApp = New clsAppEvents
Set clsApp.xlApp = Application
End Sub

Sub OpenWb()
sName = Workbooks.Add.Name
End Sub


In Class Module clsAppEvents:
--------------------------------------------

Public WithEvents xlApp As Excel.Application
Public cNAME As String

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
'Const cNAME As String = "book2" ' << CHANGE
cNAME = sName

If Left$(CStr(Wb.Name), Len(cNAME)) = cNAME Then
If MsgBox("Sure you want to close " & Wb.Name & " ?", _
vbYesNo Or vbQuestion) < vbYes Then
Cancel = True
End If
End If

End Sub
----------------------------------------

Looks like you can get rid of cName entirely, and simply

If Left$(CStr(Wb.Name), Len(sName)) = sName Then

where sName is public in the normal module

Regards,
Peter T