hmmmm

Well I won't be drawn into debates. Especially when you're comments are
unnecessarily rude.

However.
You clearly mis-understood the intention of my code. It was aimed at giving
some instruction as to how to raise application level events. Of course
there atre many ways to skin a cat...and I could as easily have writtem this
in C#.
The code given was clear and concise. It would have enabled somebody who
hadn't seen this kind of code soke ideas. It may have offered a solution to
other readers too.

"keepITcool" wrote:

Patrick

I can't seem to get this across to 'the establishment'

it is a COMPLEX example of how to create an application level
event handler.

THIS is a simple example.:

'Code in workbook object module
Option Explicit
Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
Cancel = MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Patrick Molloy wrote :

You can trap an application level event for this

Here's an example XLA

1) start a new workbook.
2) add a class module,name it clXL
3) in the class module code page addthis

Option Explicit
Private WithEvents xl As Excel.Application
Private Sub xl_WorkbookBeforeClose(ByVal Wb As Workbook, _

Cancel As Boolean)
Cancel = _
(MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo)
End Sub
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub

4) add a standard code module
5) in the code sheet of the standard code module add this code:
Option Explicit
Public xl As clXL
Sub Auto_Open()
Set xl = New clXL
End Sub

6) save the workbook as an XLA, remember where as we'll open it again
soon!

close excel
Open excel.
with the add-in manager open the XLA...browse to it if need be

When the add-in opens, the auto_open sub creates the variable set to
the open instance of excel.
Closing a workbook or excel itself will raise a message.

This is a simple example to demonstrate how simple it is to use
application level events.




"Tempy" wrote:

Hi all,

I have written some code that automatically hides all tool bars on
opening. I then have an exit button that takes it to some more code
that shows the tool bars again. However some people have closed the
workbook by just clicking on the "X" close button which does not
repair the workbook and when they re-open exel the tool bars are
not there !! Is it possible to either hide or deactivate the "X"
button ?

Thanks for all the help on my last queries

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Patrick,

Reading back I can see my phrasing was blunt. I never intended to be
rude. Apologies.

Isn't a discussion about the merits /demerits of a certain approach
more interesting than the tons of Q&A about 'runtime error 1004'?

Dont think of it as 'drawn into a debate', but sharing your knowledge
and experience. <g

Are there any disadvantages to using thisworkbook object module as the
container of application event code?



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Patrick Molloy wrote :

hmmmm

Well I won't be drawn into debates. Especially when you're comments
are unnecessarily rude.

Hi KeepITcool,

Are there any disadvantages to using thisworkbook object module as the
container of application event code?

To me, it comes down to encapsulation - having one module do one thing
and do it well, so I can copy it around and reuse it in other projects
almost unchanged. So having one class that handles application-level
events and only handles application-level events makes more sense to me
than having a class that contains a mixture of book-level and app-level
events.

On the other hand, if I think functionally rather than in terms of
'levels', I could easily imagine a class that does a specific
(high-level) 'job', but to do that, it needs to respond to both
app-level and book-level events.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Hi Stephen,

valid reasoning <g.
thx for sharing your thoughts.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Stephen Bullen wrote :

Hi KeepITcool,

Are there any disadvantages to using thisworkbook object module as
the container of application event code?

To me, it comes down to encapsulation - having one module do one
thing and do it well, so I can copy it around and reuse it in other
projects almost unchanged. So having one class that handles
application-level events and only handles application-level events
makes more sense to me than having a class that contains a mixture of
book-level and app-level events.

On the other hand, if I think functionally rather than in terms of
'levels', I could easily imagine a class that does a specific
(high-level) 'job', but to do that, it needs to respond to both
app-level and book-level events.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk