HOW TO DISABLE ALL MACROS in BeforeClose event handling procedure

Hello,

Does anyone know how to set Excel to NOT to run any macros? or how to
disable all macros? I need to do it inside BeforeClose event handling
procedure...

Thank you.


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870532558

Application.EnableEvents = False

--
HTH

Bob Phillips

"Intellihome" <Intellihome.1qkq0j@no-mx.msusenet.com> wrote in message
news:Intellihome.1qkq0j@no-mx.msusenet.com...
>
> Hello,
>
> Does anyone know how to set Excel to NOT to run any macros? or how to
> disable all macros? I need to do it inside BeforeClose event handling
> procedure...
>
> Thank you.
>
>
> --
> Intellihome
> ------------------------------------------------------------------------
> Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
> View this thread: http://www.msusenet.com/t-1870532558
>

Hi Bob,

Application.EnableEvents works only for that line where it is executed.

VBA goes to the next line and it switches EnableEvents back to True. So
this does not work.

But thanks any way.

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870532558

"Intellihome" <Intellihome.1qkvkk@no-mx.msusenet.com> wrote in message
news:Intellihome.1qkvkk@no-mx.msusenet.com...
> Application.EnableEvents works only for that line where it is executed.
>
> VBA goes to the next line and it switches EnableEvents back to True. So
> this does not work.

Completely incorrect!

--

Vasant

Hello Vasant,

How do you explain this, I took it from Microsoft Support site.
here is a quote

CAUSE
This behavior can occur because, during Automation, each line of code
that is sent to Excel to be run from an Automation client is treated as
a separate Excel macro. The EnableEvents property is turned off (set to
False) only for that one line of code, and is automatically turned back
on (set to True) for the next line of code that is sent to Excel.
Therefore, using the EnableEvents property in this context is not an
effective strategy.

and here is a link to a full article.
http://support.microsoft.com/default...b;en-us;211626

Although it is for Automation Server, I have exactly the same problem.
May be I am wrong, then why EnableEvents does not work at all?

SIncerely,

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870532558

That article is referring to using Excel as an automation server, and for
Excel 2000 (do you have 2000?). You did not mention that you were doing
this, using Excel as an automation server. When using VBA within a hosted
Excel application, that statement is totally incorrect as Vasant says.

--
HTH

Bob Phillips

"Intellihome" <Intellihome.1qlf0n@no-mx.msusenet.com> wrote in message
news:Intellihome.1qlf0n@no-mx.msusenet.com...
>
> Hello Vasant,
>
> How do you explain this, I took it from Microsoft Support site.
> here is a quote
>
> CAUSE
> This behavior can occur because, during Automation, each line of code
> that is sent to Excel to be run from an Automation client is treated as
> a separate Excel macro. The EnableEvents property is turned off (set to
> False) only for that one line of code, and is automatically turned back
> on (set to True) for the next line of code that is sent to Excel.
> Therefore, using the EnableEvents property in this context is not an
> effective strategy.
>
> and here is a link to a full article.
> http://support.microsoft.com/default...b;en-us;211626
>
> Although it is for Automation Server, I have exactly the same problem.
> May be I am wrong, then why EnableEvents does not work at all?
>
> SIncerely,
>
> Ivan
>
>
> --
> Intellihome
> ------------------------------------------------------------------------
> Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
> View this thread: http://www.msusenet.com/t-1870532558
>

Hello Bob,

No, I am not using Automation Server, but EnableEvents works as
described in this article. and I do not know what the problem with it.
It just does not work. If that is not a problem then what?

I can have for example a procedure that handles ComboBox1_Change() then
somewhere else in the code I can say:

Application.EnableEvents = False

Sheets("WhatEver").ComboBox1.Value = 1

^^^^^^^^^^^^^^^^^^^^^^^^
after this line event will be intercepted by event handler and handling
procedure will be executed, even though EnableEvents is set to False.

Why do you think this could be?

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870532558

I don't know. Could you post the workbook somewhere on the web (not this
NG)?

--
HTH

Bob Phillips

"Intellihome" <Intellihome.1qmcch@no-mx.msusenet.com> wrote in message
news:Intellihome.1qmcch@no-mx.msusenet.com...
>
> Hello Bob,
>
> No, I am not using Automation Server, but EnableEvents works as
> described in this article. and I do not know what the problem with it.
> It just does not work. If that is not a problem then what?
>
> I can have for example a procedure that handles ComboBox1_Change() then
> somewhere else in the code I can say:
>
> Application.EnableEvents = False
>
> Sheets("WhatEver").ComboBox1.Value = 1
>
> ^^^^^^^^^^^^^^^^^^^^^^^^
> after this line event will be intercepted by event handler and handling
> procedure will be executed, even though EnableEvents is set to False.
>
> Why do you think this could be?
>
> Ivan
>
>
> --
> Intellihome
> ------------------------------------------------------------------------
> Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
> View this thread: http://www.msusenet.com/t-1870532558
>