Running Macros based on Worksheet changes in Excel 2007

**Kaigi** · 04-15-2010, 12:17 AM

Hello,

I ran into a problem with a macro created in Excel 2002 that runs when the values of certain cells in the worksheet changes using

Please Login or Register  to view this content.

However, when I tested this in Excel 2007, it doesn't initiate the macro. Whats causing it to not work in 2007 even though it works in 2002?

Thanks.

**Trebor76** · 04-15-2010, 02:34 AM

Hi Kaigi,

Works fine for me

Are you sure the code (event macro) is assigned to the worksheet in question and not just put into a stand alone module?

Regards,

Robert

**DonkeyOte** · 04-15-2010, 02:53 AM

If in XL2007 the code is in the appropriate object in VBE (per prior post) check you have Events enabled..

To test this enter the below into the Immediate window:

Please Login or Register  to view this content.

If it returns False add the following line

Please Login or Register  to view this content.

**Kaigi** · 04-15-2010, 10:44 AM

Thanks for your responses.

The event macro is in the worksheet itself and not in a separate module.

Is there a way to check whether events are enabled without going into the code? The user is supposed to be able to just open the file and any changes to the worksheet should trigger the macro, so for normal use, Excel doesn't run any macros where I can enter the code to enable events before the event macro is to be triggered.

**DonkeyOte** · 04-15-2010, 10:58 AM

First off, in 2007, have you enabled Macros ?
2007 security is slightly different, see: http://office.microsoft.com/en-us/ex...969191033.aspx

Regards Events... Events would pretty much always be Enabled ... however, if you have code present that toggles this setting and which has at some point within the XL session failed and in so doing failed to reinstate Events (ie unhandled error) then it's possible Events are disabled.

First though - check your security settings.

**Kaigi** · 04-15-2010, 05:52 PM

I had previously set the security to enable all macros because before, even though it was set to Disable all macros with notification, I didn't get notified and they would just be disabled. However, even after the security was set to enable all, it didn't work yesterday.

I tried to get the macro to run later again today and it worked. I'm not sure what it was that was stopping it before, but it seems to be working fine now. Thanks.