Peter T
11/18/2009 8:32:00 PM
"noname" <sifar786@gmail.com> wrote in message
news:e4bb4385-a594-4a0e-9c55-d62d96e04693@k13g2000prh.googlegroups.com...
On Nov 19, 12:13 am, noname <sifar...@gmail.com> 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" <sifar...@gmail.com> wrote in message
>
> >news:03e6584d-97e3-44ba-ac81-cb9cd4e02dd7@u16g2000pru.googlegroups.com...
>
> > > 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