Workbook events not working but Application.EnableEvents = True!!!

[pobo1234]

Hi
I've got a major issue on a machine where none of the ThisWorkbook or worksheet events are triggering such as Workbook_SheetBeforeDoubleClick are working.
I have checked the setting for Application.EnableEvents and that is definitely true. I think an Excel dll or setting something is corrupted but can't fix it. I've tried re-setting the Excel registry settings, repairing MS Office and deleting all .exd files but none of this has worked.

Please has anyone in the forum got any ideas on what I can try or do to resolve this issue?


Many thanks

Pobo1234

 

[starl]

I've never heard of this happening. Are you sure you have setup the events properly and that, if turned off, they're turned back on? sample code?

 

[pobo1234]

I get the same problem if I open a fresh workbook and put a simple msgbox "hello" piece of code in the thisworkbook double click event procedure. When I double click a worksheet nothing happens. In fact none of the workbook events are trigger by any of the events that they should be.

 

[starl]

Don't mean to be insulting - but are you setting up the events properly? That's why I asked for a sample..
They're in the right module, they have the correct headers..
and when you type ?Application.EnableEvents in the Immediate Window, it returns True?

 

[pobo1234]

You're not be insulting but the answer is yes to all of the above. I did state in my first post that I have checked the value of application.enableevents. What we are looking at here is some sort of corruption. Does anyone know what library controls the workbook events?

 

[starl]

Yes, I know you mentioned it all earlier, but since I'm unfamiliar with you and your skill set, you could be saying things you don't really understand.. it's happened before (this month, as a matter of fact!). I will link this to the MVPs since most aren't going to see it anymore. Some of them are more experienced in the background of Excel.

 

[Jerry Sullivan]

You're not be insulting but the answer is yes to all of the above. I did state in my first post that I have checked the value of application.enableevents. What we are looking at here is some sort of corruption. Does anyone know what library controls the workbook events?

Some additional checks to understand the problem better and rule out other possible causes....

1. Are you able to run non-event macros in the same workbook?

2. Please post the double click code example (as Tracy asked).

3. If the code is in your ThisWorkbook module, the declaration should look like this...

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Sometimes people paste code examples into ThisWorkbook that were intended to work in Sheet code modules with declarations like...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

4. Make sure you have an Option Explicit statement at the top of your ThisWorkbook module, then compile the VBA project.

5. Try saving the workbook with the test event code and testing the workbook on another computer.

6. Uninstall all Excel Add-Ins including COM Add-Ins before testing.

7. If that computer/ user profile has a Personal.xlsb or other files in the XLSTART folder, temporarily move those to another folder before testing.

8. In the VB Editor, check for Missing library references. Tools > References > look for references that start with "MISSING: "

 

[RoryA]

I'd also suggest a reboot of the problem machine just in case, then start Excel in Safe mode by holding down the Ctrl key while starting the application. Then run Jerry's tests.

 

[Greg Truby]

I get the same problem if I open a fresh workbook and put a simple msgbox "hello" piece of code in the thisworkbook double click event procedure. When I double click a worksheet nothing happens. In fact none of the workbook events are trigger by any of the events that they should be.

1. Are you able to run non-event macros in the same workbook?

6. Uninstall all Excel Add-Ins including COM Add-Ins before testing.

7. If that computer/ user profile has a Personal.xlsb or other files in the XLSTART folder, temporarily move those to another folder before testing.

8. In the VB Editor, check for Missing library references. Tools > References > look for references that start with "MISSING: "

I'd alter Jerry's trouble-shooting sequence a bit. Based on your quote I cited, then Jerry's #1 will tell you whether macro security is what's getting in the way.

If macro security isn't getting in your way then perhaps something else is intercepting events first? I know I write add-ins that intercept application-level events, so it's possible that another add-in is doing this.

 

[pobo1234]

Some additional checks to understand the problem better and rule out other possible causes....

1. Are you able to run non-event macros in the same workbook?

2. Please post the double click code example (as Tracy asked).

3. If the code is in your ThisWorkbook module, the declaration should look like this...

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Sometimes people paste code examples into ThisWorkbook that were intended to work in Sheet code modules with declarations like...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

4. Make sure you have an Option Explicit statement at the top of your ThisWorkbook module, then compile the VBA project.

5. Try saving the workbook with the test event code and testing the workbook on another computer.

6. Uninstall all Excel Add-Ins including COM Add-Ins before testing.

7. If that computer/ user profile has a Personal.xlsb or other files in the XLSTART folder, temporarily move those to another folder before testing.

8. In the VB Editor, check for Missing library references. Tools > References > look for references that start with "MISSING: "

Hi Jerry

Thanks for your response

1. Are you able to run non-event macros in the same workbook? Yes

5. Try saving the workbook with the test event code and testing the workbook on another computer. - It does work on other PCs so it seems th problem is PC specific

6. Uninstall all Excel Add-Ins including COM Add-Ins before testing. - I had tried this but had left one Addin, a Reuters addin installed. On uninstalling this I was able to get the events working!!

Thanks for help again - resolved