STOP close button on another workbook

[noname]

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?

 

[Malik]

Hi,

Try to put message box in WorkBook 2 in this event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

 

[Peter T]

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]

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

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 ????

 

[Peter T]

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

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 ????

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


Hello noname,

It worked fine for me every time when I tested it. Looking again at the code
I posted cName is defined correctly (although obviously change to suit).
However I notice this line appears wrapped in my previous post

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

.... be sure to unwrap it correctly so 'Boolean' is on the same line after
'Cancel As', and


If you 'stop' the code or the code breaks, which could indeed happen with
some error, the module level variable clsApp will lose scope causing the
class object be destroyed. If something like that occurs you will need run
StartAppEvents() again to recreate the class object and the reference to
'Application' assigned to xlApp.

Regards,
Peter T

 

[noname]

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

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

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



Thanks and best regards

 

[Peter T]

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

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

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