I have an xlsm workbook which currently uses Auto_Open to perform some initialization processing which includes display of a UserForm and associated handlers for the buttons on the UserForm. I'm trying to convert from using Auto_Open (which I'm told is "obsolete") to using a Workbook_Open event handler within the Thisworkbook module within the list of Microsoft Excel Objects. I cut/pasted the logic from the Auto_Open routine within my code module into the Private Sub Workbook_Open() within the Excel Objects module, then deleted the shell of the Auto_Open routine.  But now when I open the workbook there is no evidence that any code within Workbook_Open is ever executed.  Is there some setting I'm missing somewhere to enable event handling?

cw

• Moved by George.Zhao CHNMicrosoft contingent staff Friday, August 28, 2015 5:39 AM

Especially frustrating because I can create a functional handler in a brand new workbook.  I just can't get the handler to work for this pre-existing workbook.  Sigh.

It is very difficult to guess what's wrong in that file. Please have a look into my profile for my email address and send me your file.

You can delete confidential data from the sheets if any.

Andreas.

Check some other code hasn't disabled events and not re-enabled them, in the Immediate window paste the following and hit enter.

?application.enableevents

Another possibility is the Shift key though that would also prevent Auto_Open, same applies to security settings.

The auto-macros such as Auto_Open are unlikely to ever be deprecated and work fine and will work even if events are disabled.

Thanks for reposting George.

Clearly, it's not some incorrectly set property setting as I had hoped it might be.  It appears to be some strange interaction between the Workbook_Open handler and code in one of the Modules, because the Open code seems to work when I remove all the other modules.  I'll do a bit more troubleshooting and, if I still get nowhere, I'll take you up on your offer to send you the file.

Thanks,

cw

Doubt if it's some other code disabling events since the Open code is my first attempt at using event handlers. 

Was told that Auto_Open was "obsolete" in another post on another forum.  I agree it would be hard to deprecate.  But thought I'd use the comment as an opportunity to learn how to work with events.

cw

What you say about the open event working when other modules are removed makes it seem even more likely some code has disabled events. Just before opening the workbook check EnableEvents as I suggested, if the workbook is being opened with code put a break just before the .Open line and check, then step through with F8.

BTW are you sure the event is not firing as opposed to not doing what you expect; include this in the first line of the open event and check the Immediate window after

Debug.Print  ThisWorkbook.Name, "open event"

Edit: No, Auto_Open is not obsolete even if the idea was once to make it so. It's just that more extensive workbook events were introduced in a later version of Excel, albeit a long time ago now.

• Edited by Peter Thornton (Excel MVP 2008-13)Moderator 17 hours 14 minutes ago