Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Disable Worksheet change event

I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this?

Thanks for the help.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 620
Default Disable Worksheet change event

Tim,

make sure that if you could meet an error condition (worksheet, range
doesn't exist for instance), that you trap it and reset events in this case.
otherwise you might switch them off period.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"John Wilson" wrote in message
...
Tim,

Application.EnableEvents = False

set it back to True when you're done with that part of the code
that would have fired the event.

John

Tim wrote:

I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this?

Thanks for the help.




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 124
Default Disable Worksheet change event

Application.EnableEvents = False will disable ALL events. If you don't want to do that, then you
can use a public variable (in a standard module)

In the standard module:
Public Flag As Boolean

Sub AnotherMacro()
Flag = True
'run your code here
Flag = False
End Sub

In ThisWorkbook module:

Sub Worksheet_Change(.....)
If Flag Then Exit Sub
'the "real" code here
End Sub

Check out the Public statement in Help for more information about variable scope.



On 14 Jul 2003 10:12:27 -0700, (Tim) wrote:

I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this?

Thanks for the help.


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 620
Default Disable Worksheet change event

John,

You are right, but Myrna points out a particular instance which may not be
appropriate, and so is expanding the bag of tricks so to speak.

As ever, its a case of horses for courses. With a full toolkit, we can pick
the most appropriate.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"John Wilson" wrote in message
...
Myrna,

Just a question of proper procedure.

Usually, just a single line of code will cause an event to fire.
If Application.EnableEvents is set to False just before that line
of code and back to True just after it, isn't this the easiest way
to go about insuring that the event won't fire?

John

Myrna Larson wrote:

Application.EnableEvents = False will disable ALL events. If you don't

want to do that, then you
can use a public variable (in a standard module)

In the standard module:
Public Flag As Boolean

Sub AnotherMacro()
Flag = True
'run your code here
Flag = False
End Sub

In ThisWorkbook module:

Sub Worksheet_Change(.....)
If Flag Then Exit Sub
'the "real" code here
End Sub

Check out the Public statement in Help for more information about

variable scope.

On 14 Jul 2003 10:12:27 -0700, (Tim) wrote:

I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this?

Thanks for the help.




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 550
Default Disable Worksheet change event

Bob,

Thanks for the heads up.
I've used coding similar to what Myrna pointed out in some of my
projects.
I questioned my post after reading yours and Myrna's thinking
that I did something wrong or there was a better way.

With a full toolkit, we can pick the most appropriate.

How very true. There are many questions in the ng's where
you could carried away writing a book with all the different ways
to answer it. There probably will never be a "full" toolkit though,
which is good so that we can keep busy "trying" to fill it.

Take care,
John

Bob Phillips wrote:

John,

You are right, but Myrna points out a particular instance which may not be
appropriate, and so is expanding the bag of tricks so to speak.

As ever, its a case of horses for courses. With a full toolkit, we can pick
the most appropriate.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

"John Wilson" wrote in message
...
Myrna,

Just a question of proper procedure.

Usually, just a single line of code will cause an event to fire.
If Application.EnableEvents is set to False just before that line
of code and back to True just after it, isn't this the easiest way
to go about insuring that the event won't fire?

John

Myrna Larson wrote:

Application.EnableEvents = False will disable ALL events. If you don't

want to do that, then you
can use a public variable (in a standard module)

In the standard module:
Public Flag As Boolean

Sub AnotherMacro()
Flag = True
'run your code here
Flag = False
End Sub

In ThisWorkbook module:

Sub Worksheet_Change(.....)
If Flag Then Exit Sub
'the "real" code here
End Sub

Check out the Public statement in Help for more information about

variable scope.

On 14 Jul 2003 10:12:27 -0700, (Tim) wrote:

I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this?

Thanks for the help.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet Change Event Tony S.[_2_] Excel Discussion (Misc queries) 11 February 18th 09 02:04 AM
Worksheet change Event ranswert Excel Worksheet Functions 1 January 18th 08 12:17 AM
Worksheet Change event LAF Excel Discussion (Misc queries) 3 January 4th 06 03:08 AM
Worksheet Change Event TonyM Excel Discussion (Misc queries) 8 March 11th 05 01:52 PM
Worksheet Row Change event crazybass2 Excel Discussion (Misc queries) 4 December 8th 04 06:29 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"