Ok, great, I finally worked it out through the switching of formulas calculation to manual. Or your file has acquired a corruption, in which case I recommend readingĬomment by: Greg (3-12-2009 03:27:35) deeplink to this comment
#VISUAL BASIC FOR EXCEL ON CHANGE EVENT CODE#
Either there is code turning off events without you realising it (do a project-wide search on that keyword and put a breakpoint on each, then turn events on using the immediate pane). In that case there is definitely something wrong with your file. Obviously I can't just come back to the previous file: too much data has been changed on the sheets.Ĭomment by: Jan Karel Pieterse (3-12-2009 02:28:21) deeplink to this comment If this can be of any help for troubleshooting, note that if I open previous versions of my file, then the event detection works fine, so the error should really be within the given file (another global variable set to a wrong value?). Showing last 8 comments of 13 in total ( Show All Comments):Ĭomment by: Greg (3-12-2009 02:22:34) deeplink to this comment Single event, just by adding more public variables: Give you finer control as to what happens, since you can switch off a Is reset to False and your events will work as expected. due to the user clicking the Endīutton on a runtime error you didn't catch), then the variable NoEvent The big advantage of this technique over using Of course if you're not closing the file, but need to disable otherĮvents, remember to set the NoEvents back to False: Of course now you need to put this to use. Add a public variable to the top of the Thisworkbook module:Īnd inside the BeforeClose event, check the value of that variable: MsgBox "You are not allowed to close this file!",īut of course you want to be able to close the file using your ownĬode. Private Sub Workbook_BeforeClose(Cancel As Boolean) You have written a Workbook_BeforeClose routine in the Thisworkbook Let's say you want to prevent your users closing your workbook. Thisworkbook module, Sheet modules and modules behind userforms are in The Thisworkbook module, but in principle any class module will do (the Inside the class module that contains the events. Programming practice (although I admit I use that technique myself too).īelow I will show you a more general approach, using a boolean variable Another one is by using a global variable, which youĬheck against inside the event module. If your code crashes,Įvents stay disabled! Another disadvantage is that it will not work for But that will disable all applicationĮvents, including event handlers add-ins may need. One of them is to useĪpplication.EnableEvents=False. There are several ways to disable event code. to avoid event looping or to enable yourĬode to do things you are preventing your user to do through the user Will probably also need to be able to control whether or not an eventĮxecutes its code or not (e.g. Make your application respond to user actions such as entering data intoĬells or clicking the print button. Control When Events Are Handled IntroductionĮvents are a powerful aspect of Excel programming.