Disable the "X" close button in an excel application

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

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

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

You could cancel the close as Patrick suggested..

however following will just Dis/Enable the system menu on the window
Note that if you plan to use it on xl97:
you'll need to change application.hwnd with a FindWindow call



Option Explicit
Private Declare Function GetWindowLong Lib "user32.dll" Alias
"GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32.dll" Alias
"SetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Sub DisableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) And Not &H80000
End With
End Sub
Sub EnableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) Or &H80000
End With
End Sub




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


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

Ah well, in for a penny, in for a pound.

Even though I may think that this is a better way of setting application
level events (see my discussion with Dave Peterson some 4 weeks ago), your
example is no less simple, it just saves on one class module. The complexity
of the solution is the same. Surely, even you would not say that 100 lines
of code is necessarily more complex than 50 lines.

Bob


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> 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 ***
> > >

Hi to both of you,

Thanks for the help, but i cannot get it to work....I fairly new to this
game and i have probably worded it incorrectly !!

It is an excel workboox that i am using, is that the same as an
application ?

Where should i put this code after the auto_Open macro ?

Tempy

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

as clearly stated in my post:

the code should go the workbook's object module.
(the code behind Thisworkbook)

during testing you may have to run the workbook_open proc
manually to (re)set the xlApp variable resp Patrick's class.

Also see my alternative (posted as a direct answer to your question
in the root of this thread) which maybe more to your liking

Since you are new:
dont spend too much time taking control away from the user.
hiding toolbars and even disabling the ability to close excel
requires your code to be absolutely bulletproof.
Else buy a bulletproof vest, cuz your users will come after you.


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


Tempy wrote :

> Hi to both of you,
>
> Thanks for the help, but i cannot get it to work....I fairly new to
> this game and i have probably worded it incorrectly !!
>
> It is an excel workboox that i am using, is that the same as an
> application ?
>
> Where should i put this code after the auto_Open macro ?
>
> Tempy
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks keepITcool,

I appreciate your help.


Tempy

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

I'm avoiding an entire class module.. thus

iso working with 2 objects (cls in thisworkbook and app in cls)
i'm working with 1 object (app in thisworkbook)

'no less simple' to those who are unfamiliar with class modules?
hmm... try more transparent <g>


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


Bob Phillips wrote :

> Ah well, in for a penny, in for a pound.
>
> Even though I may think that this is a better way of setting
> application level events (see my discussion with Dave Peterson some 4
> weeks ago), your example is no less simple, it just saves on one
> class module. The complexity of the solution is the same. Surely,
> even you would not say that 100 lines of code is necessarily more
> complex than 50 lines.
>
> Bob
>
>
> "keepITcool" <[email protected]> wrote in message
> news:[email protected]...
> > 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 ***
> > > >

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> I'm avoiding an entire class module.. thus
>
> iso working with 2 objects (cls in thisworkbook and app in cls)
> i'm working with 1 object (app in thisworkbook)
>
> 'no less simple' to those who are unfamiliar with class modules?

Those unfamilar with class modules will probably be just as unfamiliar that
Thisworkbook is a class module, and with application events. In most
instances, they will want a solution, and bullet proof instructions to
implement it. Accusing Patrick and 'the estrablishment' (whatever that is)
of being unable to comprehend this seeming (to you) axiomatic truth does not
help the poster in any way. Patrick's solution achieves the same result as
yours.

Your point is a good point (IMO), but you cloud it with the way it is
raised.

> hmm... try more transparent <g>

Sorry, I am not being obtuse (really <g>), but it is no more transparent
either. It is better, IMO as well as yours, as it saves on that unnecessary
class module, but the complexity factor is still there, the application
events voodoo is still there. Some even think the extra class module is
better, as I found when I madethis case some weeks ago.

I've had similar discussions in the past, but sofar nobody explained me
why separate class would be better.

I had no luck trying to find that thread you mentioned.
Do you remember it's topic?

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


Bob Phillips wrote :

>
> "keepITcool" <[email protected]> wrote in message
> news:[email protected]...
> >
> > <snip> Some even think
> the extra class module is better, as I found when I madethis case
> some weeks ago.

Well Jan Karel has just given one reason (which again I can't say I ascribe
to), and the thread in question can be found at http://tinyurl.com/8ndb9.
You will probably smile at the reason, but as he says, it is just a personal
choice.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> I've had similar discussions in the past, but sofar nobody explained me
> why separate class would be better.
>
> I had no luck trying to find that thread you mentioned.
> Do you remember it's topic?
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Bob Phillips wrote :
>
> >
> > "keepITcool" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > <snip> Some even think
> > the extra class module is better, as I found when I madethis case
> > some weeks ago.

Hi Bob,

> It is better, IMO as well as yours, as it saves on that unnecessary
> class module,
>

I must say I disagree on that point.

Class modules can easily be cleaned by an export/remove/import cycle
(or by using Rob Bovey's code cleaner), but the Thisworkbook module
cannot be removed.

So when a bloat creeps into the Thisworkbook module, one will have to
start with a fresh workbook to remove it.

This my main reason why I try to keep the Thisworkbook module as slim
as I possibly can.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

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

OFF TOPIC?

Hi Jan Karel,

I've never knew you could have bloat in thisworkbook..

I honestly thought codebloat could only exist in userforms, where the
compiled parts (the frx) contains dirt from unused objects.
(I've also had some troublesome experiences with FlexGrids, which have
some nasty habits)

I can imagine the flexgrid problems popping up in sheets, but then that
would bloat the sheet object being the container of the troublesome
activeX... and i doubt the problems would be solved by exporting/
importing the code as that wouldnt affect the sheet's objects.
(I'll test that)

Codebloat in normal or class modules theoretically doesnt make sense.
(note that some filereduction from CodeCleaner is achieved simply by
saving it in decompiled state)

You wouldn't have an example of bloated code (in a plain or workbook
module) somewhere on your harddisk, would you?

I'd love to have a look.


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


Jan Karel Pieterse wrote :

> Hi Bob,
>
> > It is better, IMO as well as yours, as it saves on that unnecessary
> > class module,
> >
>
> I must say I disagree on that point.
>
> Class modules can easily be cleaned by an export/remove/import cycle
> (or by using Rob Bovey's code cleaner), but the Thisworkbook module
> cannot be removed.
>
> So when a bloat creeps into the Thisworkbook module, one will have to
> start with a fresh workbook to remove it.
>
> This my main reason why I try to keep the Thisworkbook module as slim
> as I possibly can.
>
> Regards,
>
> Jan Karel Pieterse
> Excel MVP
> http://www.jkp-ads.com

Hi KeepITcool,

> Codebloat in normal or class modules theoretically doesnt make sense.
> (note that some filereduction from CodeCleaner is achieved simply by
> saving it in decompiled state)

It happens though. I have seen a couple of workbooks of mine go
haywire, which mostly could be fixed by a code cleaning job.
Mostly it requires Modules that contain quite some code (50K or more)
and have been heavily edited in the past without ever cleaning up.

One was even corrupted so bad I had to copy the code to Notepad and
paste that back after inserting a new module.

The symptom it showed was that I would get a compile error (object
doesn't support this property or method!) on this kind of line:

sTemp=Thisworkbook.worksheets("Sheet1").Range("A1").Value

Note the lack of capitalisation of the worksheets collection!

> You wouldn't have an example of bloated code (in a plain or workbook
> module) somewhere on your harddisk, would you?

Sorry, this was a customer workbook I cannot share.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

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

Hi keepITcool,

I have Inserted your code into my object module but the sentance, "Dim
WithEvents xlApp As Application", keeps giving me an error.

Could you possibly help ?

Thanks

Tempy

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

I dont know what you consider 'my object module'
my code is meant to go in the code module of Thisworkbook object.
NOT in a normal module. that was the whole point.


open VBE
double click ThisWorkbook in Project Explorer.
a code pane opens (or is activated) titled:
BookX.xls - ThisWorkbook[Code]

copy my code there. (and remove it anywhere else)

now you should be able to use the dropdowns at the top
of the codepane and select the General and the objects workbook and
xlapp

... note if you select xlApp in the LEFT dropdown..
the available events are listed on the Right.


From VBA help:

WithEvents Optional. Keyword that specifies that varname is an object
variable used to respond to events triggered by an ActiveX object.

WithEvents is valid only in class modules.

You can declare as many individual variables as you like using
WithEvents, but you can't create arrays with WithEvents. You can't use
New with WithEvents.

Re 'WithEvents is valid only in class modules.':
modules behind a userform, worksheet or thisworkbook
are defacto classmodules.






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


Tempy wrote :

> Hi keepITcool,
>
> I have Inserted your code into my object module but the sentance, "Dim
> WithEvents xlApp As Application", keeps giving me an error.
>
> Could you possibly help ?
>
> Thanks
>
> Tempy
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi KeepITcool,

Thanks very much, i found the problem and it working just great, thanks
for all the help and understanding.

I seem to have created quite a bit of dialog with this subject!!

Thanks again for a great job to all.

best regards,

Tempy

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

Troublemaker :-)


"Tempy" <[email protected]> wrote in message
news:[email protected]...
> Hi KeepITcool,
>
> Thanks very much, i found the problem and it working just great, thanks
> for all the help and understanding.
>
> I seem to have created quite a bit of dialog with this subject!!
>
> Thanks again for a great job to all.
>
> best regards,
>
> Tempy
>
> *** Sent via Developersdex http://www.developersdex.com ***