How to disable all macros initially when i open the workbook

How to disable all macros initially when i open the workbook

Post by Mangesh Dayn » Wed, 06 Jun 2012 23:52:54



Hi Friends,

I am working with the file containing macros.
I want all macros should be disable when i opened the file and should be
enable when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.
Please help.

MAnGesh

 
 
 

How to disable all macros initially when i open the workbook

Post by dguillett » Thu, 07 Jun 2012 00:10:40


You probably have your macro in the ThisWorkbook workbook open  or a sheet module activate. Move the body of the macro to a regular Sub linked to your command button or a shape.

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: Mangesh Dayne
Sent: Tuesday, June 05, 2012 9:52 AM

Subject: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi Friends,

I am working with the file containing macros.
I want all macros should be disable when i opened the file and should be enable when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button.
Please help.

MAnGesh
--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

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


 
 
 

How to disable all macros initially when i open the workbook

Post by Rajan_Verm » Thu, 07 Jun 2012 00:34:50


Select the option Disable all macros with Notification.. you will get a
notice to enable macros , once you will click on that button you will get
your macros enabled

HTH

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23

Subject: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook

Hi Friends,

I am working with the file containing macros.

I want all macros should be disable when i opened the file and should be
enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.

Please help.

MAnGesh

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to

  image001.png
226K Download
 
 
 

How to disable all macros initially when i open the workbook

Post by David Grugeo » Thu, 07 Jun 2012 14:44:09


Hi Mangesh

I think it depends on how the macros are started.  If they are started by
event such as worksheet_change you will need to dothis.

In a general module put the following before the subs just under the Option
Explicit

Public RunMacros as boolean

then in each macro immediately after the sub declaration you put

If not RunMacros then exit sub

Like this

Private Sub Worksheet_Change()
If not RunMacros then exit sub
<your code>
End Sub

Now you need a command button and make its click event like

Private Sub CommandButton1_Click()
RunMacros = Not RunMacros
End Sub

Each time you click the button you switch from allowing macros to not
allowing them.
Because booleans are initialised as false it will start off not allowing
them.

The extra line has to go in each event driven macro.


> ** **

> Select the option Disable all macros with Notification.. you will get a
> notice to enable macros , once you will click on that button you will get
> your macros enabled****

> ** **

> ** **

> ****

> ** **

> * *

> *HTH*

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **



> *Sent:* 05 June 2012 8:23


> *Subject:* $$Excel-Macros$$ How to disable all macros initially when i
> open the workbook****

> ** **

> Hi Friends,****

> ** **

> I am working with the file containing macros.****

> I want all macros should be disable when i opened the file and should be
> enable when i click on command button in file.****

> My intention is to stop executing the macro just after opening the file.
> It should be run only when i click on command button.****

> Please help.****

> ** **

> ** **

> MAnGesh****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
David Grugeon

  image001.png
226K Download
 
 
 

How to disable all macros initially when i open the workbook

Post by Rajan_Verm » Thu, 07 Jun 2012 23:24:02


Hi  David,

Are you saying to write these code on all worksheets module?

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of David Grugeon
Sent: 06 June 2012 11:14

Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i
open the workbook

Hi Mangesh

I think it depends on how the macros are started.  If they are started by
event such as worksheet_change you will need to dothis.

In a general module put the following before the subs just under the Option
Explicit

Public RunMacros as boolean

then in each macro immediately after the sub declaration you put

If not RunMacros then exit sub

Like this

Private Sub Worksheet_Change()

If not RunMacros then exit sub

<your code>

End Sub

Now you need a command button and make its click event like

Private Sub CommandButton1_Click()

RunMacros = Not RunMacros

End Sub

Each time you click the button you switch from allowing macros to not
allowing them.

Because booleans are initialised as false it will start off not allowing
them.

The extra line has to go in each event driven macro.


Select the option Disable all macros with Notification.. you will get a
notice to enable macros , once you will click on that button you will get
your macros enabled

HTH

Regards

Rajan verma

+91 7838100659 <tel:%2B91%207838100659>  [IM-Gtalk]


On Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23


Subject: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook

Hi Friends,

I am working with the file containing macros.

I want all macros should be disable when i opened the file and should be
enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.

Please help.

MAnGesh

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to


--
David Grugeon

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to

  image001.png
226K Download
 
 
 

How to disable all macros initially when i open the workbook

Post by Paul Schreine » Thu, 07 Jun 2012 23:53:01


There's a bit of confusion about how you describe what you're looking for.
You said:
I want all macros should be disable when i opened the file and should be enable
when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.

The fact is, if you "DISABLE" macros, then you cannot run the macro from a
Command Button.
So, answers that change the way macros are enabled/disabled for EXCEL aren't
going to help, because
the notification starts up as soon as you open the file.?
The choice you make from this notice will affect how the macros are treated.
If you elect to disable, then all macros in this file will not work (including a
command button)
If you ENABLE, then the startup macro will immediately execute, which seems to
be what you're trying to avoid..

I think what you're INTENDING is:
-You have a file with macros
-It has a startup macro.
-You want to change this startup macro to be executed via a Command Button
instead of when the file is opened.

There are two ways a macro can start when you open a workbook:
Workbook_Open event macro (located in the ThisWorkbook module)
Auto_Open macro (located in a "Standard" module, rather than a Sheet module)

Now, if the macro is in a Auto_Open macro, simply rename it to something else
(like Btn_Execute)
Then, create your Command Button, and have it execute this macro.
(technique varies depending on the type of Command Button: ActiveX, or Form)

If it is running from a Workbook_Open event, then you need to examine the event
macro and determine if the macro is completely contained in the even macro or is
it actually in a "standard" module and called from the event macro.. like:

Private Sub Workbook_Open()
??? Btn_Command
End Sub

If it's completely in the Workbook_Open macro, you'll have to cut/paste it into
a macro in a "Sheet" module, remove the Workbook_Open event macro completely and
create/assign the button as described above...

hope this helps.

Paul
-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

________________________________


Sent: Wed, June 6, 2012 10:24:32 AM
Subject: RE: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook

Hi? David,
?
Are you saying to write these code on all worksheets module?
?
?
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
?

Behalf Of David Grugeon
Sent: 06 June 2012 11:14

Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook
?
Hi Mangesh

I think it depends on how the macros are started. ?If they are started by event
such as worksheet_change you will need to dothis.
?
In a general module put the following before the subs just under the Option
Explicit
?
Public RunMacros as boolean
?
then in each macro immediately after the sub declaration you put?
?
If not RunMacros then exit sub
?
Like this
?
Private Sub Worksheet_Change()
If not RunMacros then exit sub
<your code>
End Sub
?
Now you need a command button and make its click event like
?
Private Sub CommandButton1_Click()
RunMacros = Not RunMacros
End Sub
?
Each time you click the button you switch from allowing macros to not allowing
them.
Because booleans are initialised as false it will start off not allowing them.
?
The extra line has to go in each event driven macro.
?
?

?
Select the option Disable all macros with Notification.. you will get a notice
to enable macros , once you will click on that button you will get your macros
enabled
?
?
?
?
HTH
?
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
?

Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23


Subject: $$Excel-Macros$$ How to disable all macros initially when i open the
workbook
?
Hi Friends,
?
I am working with the file containing macros.
I want all macros should be disable when i opened the file and should be enable
when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.
Please help.
?
?
MAnGesh
--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------


?

--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------


?

?
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------


?
To unsubscribe, send a blank email to

FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------


?

 
 
 

How to disable all macros initially when i open the workbook

Post by dguillett » Fri, 08 Jun 2012 00:09:04


Paul, This is what I suggested but didnt hear back??

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: Paul Schreiner
Sent: Wednesday, June 06, 2012 9:53 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

There's a bit of confusion about how you describe what you're looking for.
You said:
I want all macros should be disable when i opened the file and should be enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button.

The fact is, if you "DISABLE" macros, then you cannot run the macro from a Command Button.
So, answers that change the way macros are enabled/disabled for EXCEL aren't going to help, because
the notification starts up as soon as you open the file.
The choice you make from this notice will affect how the macros are treated.
If you elect to disable, then all macros in this file will not work (including a command button)
If you ENABLE, then the startup macro will immediately execute, which seems to be what you're trying to avoid..

I think what you're INTENDING is:
-You have a file with macros
-It has a startup macro.
-You want to change this startup macro to be executed via a Command Button instead of when the file is opened.

There are two ways a macro can start when you open a workbook:
Workbook_Open event macro (located in the ThisWorkbook module)
Auto_Open macro (located in a "Standard" module, rather than a Sheet module)

Now, if the macro is in a Auto_Open macro, simply rename it to something else (like Btn_Execute)
Then, create your Command Button, and have it execute this macro.
(technique varies depending on the type of Command Button: ActiveX, or Form)

If it is running from a Workbook_Open event, then you need to examine the event macro and determine if the macro is completely contained in the even macro or is it actually in a "standard" module and called from the event macro.. like:

Private Sub Workbook_Open()
    Btn_Command
End Sub

If it's completely in the Workbook_Open macro, you'll have to cut/paste it into a macro in a "Sheet" module, remove the Workbook_Open event macro completely and create/assign the button as described above...

hope this helps.

Paul

-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

--------------------------------------------------------------------------------
From: Rajan_Verma <rajanverma1...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, June 6, 2012 10:24:32 AM
Subject: RE: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi  David,

Are you saying to write these code on all worksheets module?

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of David Grugeon
Sent: 06 June 2012 11:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi Mangesh

I think it depends on how the macros are started.  If they are started by event such as worksheet_change you will need to dothis.

In a general module put the following before the subs just under the Option Explicit

Public RunMacros as boolean

then in each macro immediately after the sub declaration you put

If not RunMacros then exit sub

Like this

Private Sub Worksheet_Change()

If not RunMacros then exit sub

<your code>

End Sub

Now you need a command button and make its click event like

Private Sub CommandButton1_Click()

RunMacros = Not RunMacros

End Sub

Each time you click the button you switch from allowing macros to not allowing them.

Because booleans are initialised as false it will start off not allowing them.

The extra line has to go in each event driven macro.

On 6 June 2012 01:34, Rajan_Verma <rajanverma1...@gmail.com> wrote:

Select the option Disable all macros with Notification.. you will get a notice to enable macros , once you will click on that button you will get your macros enabled

HTH

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23

To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi Friends,

I am working with the file containing macros.

I want all macros should be disable when i opened the file and should be enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button.

Please help.

MAnGesh

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscribe@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to mailto:excel-macros%2Bunsubscribe@googlegroups.com

--
David Grugeon

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscribe@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscribe@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to ...

read more »

  image001.png
226K Download
 
 
 

How to disable all macros initially when i open the workbook

Post by Paul Schreine » Fri, 08 Jun 2012 00:47:44


Sorry?Don,

I have a new computer,?and for some reason Outlook was pushing some (but not
all)?of the Excel-macro emails to the?spam folder.
So I didn't see some of the responses.

ones I DID see I misinterpreted as being the originator's response to attempting
to follow the suggestion to disable all macros.

It looks like the originator hasn't?actually responded to anyone's help.
the reponses are actually members?e-talking amongst themselves.

Your answer was simple, concise and easy to follow... for me...

However, based on how the original question was phrased, I suspect that Mangesh
may not understand event macros or perhaps the distinction between sheet modules
and "standard" modules...

BTW (not to quibble) but your answer:
"You probably have your macro in the ThisWorkbook workbook open? or a sheet
module activate.?"
isn't 100% accurate...

A Worksheet_Activate event would not?"automatically" run when you OPEN a
workbook. (I tested it to be sure)

As far as I know, only the?Thisworkbook.Workbook_Open and?an Auto_Open macros
will run when a workbook is opened.
?
Paul
-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

________________________________
From: dguillett1 <dguille...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, June 6, 2012 11:13:29 AM
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook

Paul, This is what I suggested but didnt hear back??

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: Paul Schreiner
Sent: Wednesday, June 06, 2012 9:53 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook
There's a bit of confusion about how you describe what you're looking for.
You said:
I want all macros should be disable when i opened the file and should be enable
when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.

The fact is, if you "DISABLE" macros, then you cannot run the macro from a
Command Button.
So, answers that change the way macros are enabled/disabled for EXCEL aren't
going to help, because
the notification starts up as soon as you open the file.
The choice you make from this notice will affect how the macros are treated.
If you elect to disable, then all macros in this file will not work (including a
command button)
If you ENABLE, then the startup macro will immediately execute, which seems to
be what you're trying to avoid..

I think what you're INTENDING is:
-You have a file with macros
-It has a startup macro.
-You want to change this startup macro to be executed via a Command Button
instead of when the file is opened.

There are two ways a macro can start when you open a workbook:
Workbook_Open event macro (located in the ThisWorkbook module)
Auto_Open macro (located in a "Standard" module, rather than a Sheet module)

Now, if the macro is in a Auto_Open macro, simply rename it to something else
(like Btn_Execute)
Then, create your Command Button, and have it execute this macro.
(technique varies depending on the type of Command Button: ActiveX, or Form)

If it is running from a Workbook_Open event, then you need to examine the event
macro and determine if the macro is completely contained in the even macro or is
it actually in a "standard" module and called from the event macro.. like:

Private Sub Workbook_Open()
??? Btn_Command
End Sub

If it's completely in the Workbook_Open macro, you'll have to cut/paste it into
a macro in a "Sheet" module, remove the Workbook_Open event macro completely and
create/assign the button as described above...

hope this helps.

Paul
-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

________________________________
From: Rajan_Verma <rajanverma1...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, June 6, 2012 10:24:32 AM
Subject: RE: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook

Hi? David,
?
Are you saying to write these code on all worksheets module?
?
?
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
?
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of David Grugeon
Sent: 06 June 2012 11:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open
the workbook
?
Hi Mangesh

I think it depends on how the macros are started.? If they are started by event
such as worksheet_change you will need to dothis.
?
In a general module put the following before the subs just under the Option
Explicit
?
Public RunMacros as boolean
?
then in each macro immediately after the sub declaration you put
?
If not RunMacros then exit sub
?
Like this
?
Private Sub Worksheet_Change()
If not RunMacros then exit sub
<your code>
End Sub
?
Now you need a command button and make its click event like
?
Private Sub CommandButton1_Click()
RunMacros = Not RunMacros
End Sub
?
Each time you click the button you switch from allowing macros to not allowing
them.
Because booleans are initialised as false it will start off not allowing them.
?
The extra line has to go in each event driven macro.
?
?
On 6 June 2012 01:34, Rajan_Verma <rajanverma1...@gmail.com> wrote:
?
Select the option Disable all macros with Notification.. you will get a notice
to enable macros , once you will click on that button you will get your macros
enabled
?
?
?
?
HTH
?
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
?
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23

To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ How to disable all macros initially when i open the
workbook
?
Hi Friends,
?
I am working with the file containing macros.
I want all macros should be disable when i opened the file and should be enable
when i click on command button in file.
My intention is to stop executing the macro just after opening the file. It
should be run only when i click on command button.
Please help.
?
?
MAnGesh
--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------

To post to this group, send email to excel-macros@googlegroups.com
?
To unsubscribe, send a blank email to excel-macros+unsubscribe@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------

To post to this group, send email to excel-macros@googlegroups.com
?
To unsubscribe, send a blank email to
mailto:excel-macros%2Bunsubscribe@googlegroups.com

?
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

?
NOTE : Don't ever post personal or confidential data in a workbook. Forum owners
and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------

To post to this group, send email to excel-macros@googlegroups.com
?
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com--

FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good ...

read more »

 
 
 

How to disable all macros initially when i open the workbook

Post by dguillett » Fri, 08 Jun 2012 01:01:35


Paul, Congrats on the new computer. I didnt mean that you  shouldnt also answer. Just pointing out that OP hadnt responded.
He could have maybe not had in the workbook open but it was fired on some sheet activation or the workbookopen event activated a sheet where it resided to do its dirty work??? Just a thought.

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: Paul Schreiner
Sent: Wednesday, June 06, 2012 10:47 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Sorry Don,

I have a new computer, and for some reason Outlook was pushing some (but not all) of the Excel-macro emails to the spam folder.
So I didn't see some of the responses.

ones I DID see I misinterpreted as being the originator's response to attempting to follow the suggestion to disable all macros.

It looks like the originator hasn't actually responded to anyone's help.
the reponses are actually members e-talking amongst themselves.

Your answer was simple, concise and easy to follow... for me...

However, based on how the original question was phrased, I suspect that Mangesh may not understand event macros or perhaps the distinction between sheet modules and "standard" modules...

BTW (not to quibble) but your answer:
"You probably have your macro in the ThisWorkbook workbook open  or a sheet module activate. "
isn't 100% accurate...

A Worksheet_Activate event would not "automatically" run when you OPEN a workbook. (I tested it to be sure)

As far as I know, only the Thisworkbook.Workbook_Open and an Auto_Open macros will run when a workbook is opened.

Paul

-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

--------------------------------------------------------------------------------
From: dguillett1 <dguille...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, June 6, 2012 11:13:29 AM
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Paul, This is what I suggested but didnt hear back??

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com

From: Paul Schreiner
Sent: Wednesday, June 06, 2012 9:53 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

There's a bit of confusion about how you describe what you're looking for.
You said:
I want all macros should be disable when i opened the file and should be enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button.

The fact is, if you "DISABLE" macros, then you cannot run the macro from a Command Button.
So, answers that change the way macros are enabled/disabled for EXCEL aren't going to help, because
the notification starts up as soon as you open the file.
The choice you make from this notice will affect how the macros are treated.
If you elect to disable, then all macros in this file will not work (including a command button)
If you ENABLE, then the startup macro will immediately execute, which seems to be what you're trying to avoid..

I think what you're INTENDING is:
-You have a file with macros
-It has a startup macro.
-You want to change this startup macro to be executed via a Command Button instead of when the file is opened.

There are two ways a macro can start when you open a workbook:
Workbook_Open event macro (located in the ThisWorkbook module)
Auto_Open macro (located in a "Standard" module, rather than a Sheet module)

Now, if the macro is in a Auto_Open macro, simply rename it to something else (like Btn_Execute)
Then, create your Command Button, and have it execute this macro.
(technique varies depending on the type of Command Button: ActiveX, or Form)

If it is running from a Workbook_Open event, then you need to examine the event macro and determine if the macro is completely contained in the even macro or is it actually in a "standard" module and called from the event macro.. like:

Private Sub Workbook_Open()
    Btn_Command
End Sub

If it's completely in the Workbook_Open macro, you'll have to cut/paste it into a macro in a "Sheet" module, remove the Workbook_Open event macro completely and create/assign the button as described above...

hope this helps.

Paul

-----------------------------------------
Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can. - John Wesley
-----------------------------------------

--------------------------------------------------------------------------------
From: Rajan_Verma <rajanverma1...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, June 6, 2012 10:24:32 AM
Subject: RE: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi  David,

Are you saying to write these code on all worksheets module?

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of David Grugeon
Sent: 06 June 2012 11:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi Mangesh

I think it depends on how the macros are started.  If they are started by event such as worksheet_change you will need to dothis.

In a general module put the following before the subs just under the Option Explicit

Public RunMacros as boolean

then in each macro immediately after the sub declaration you put

If not RunMacros then exit sub

Like this

Private Sub Worksheet_Change()

If not RunMacros then exit sub

<your code>

End Sub

Now you need a command button and make its click event like

Private Sub CommandButton1_Click()

RunMacros = Not RunMacros

End Sub

Each time you click the button you switch from allowing macros to not allowing them.

Because booleans are initialised as false it will start off not allowing them.

The extra line has to go in each event driven macro.

On 6 June 2012 01:34, Rajan_Verma <rajanverma1...@gmail.com> wrote:

Select the option Disable all macros with Notification.. you will get a notice to enable macros , once you will click on that button you will get your macros enabled

HTH

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mangesh Dayne
Sent: 05 June 2012 8:23

To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ How to disable all macros initially when i open the workbook

Hi Friends,

I am working with the file containing macros.

I want all macros should be disable when i opened the file and should be enable when i click on command button in file.

My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button.

Please help.

MAnGesh

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscribe@googlegroups.com

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to mailto:excel-macros%2Bunsubscribe@googlegroups.com

--
David Grugeon

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or ...

read more »

  image001.png
226K Download
 
 
 

1. How to hide workbook if user chooses "disable macros"

I'm someone with only limited experience with doing things in Excel with
VBA, and I need help on a particular part of what should be a fairly simple
task.  I need to ensure that a user printing anything from a workbook gets a
particular header (a legal disclaimer, of course), and can't disable the
header.  I used a BeforePrint event to get the header on, and
password-protected the project so a user couldn't go into the code and
change it.  But I know if the user chooses "Disable Macros" when the
workbook launches, I'm dead in the water.  Is there any way to code it so
that if he chooses "disable macros" he just can't open the workbook (or
everything gets hidden)?

Thanks for any help you can provide.

Stacey

2. form validation encryption error

3. disabled macros in a workbook due to high security settings

4. C++/Java/VB Source Code Resource: http://www.immaculatesoftware.com/codeonthenet

5. EXCEL 5: CALLING A MACRO IN ANOTHER OPEN WORKBOOK

6. Some @home observations (and replies) ...

7. Start a Macro on Workbook Opening??

8. FS: Sun Sparc 2 Complete System

9. Macro pauses if opening a workbook with links

10. Copy Multiple Workbooks into One Workbook Macro problem ...

11. key combination to disable auto-open macro

12. How do I disable Macro protection when opening project programmatically.

13. opening an already opened workbook