Workbook_Open ignores On Error GoTo. How to fix this?

[XmisterIS]

Try this:

Please Login or Register  to view this content.

Now run this in the developer window.

The message box pops up as you would expect.

Now move the sub to ThisWorkbook and change its name to Workbook_Open ... The sub now behaves as if On Error GoTo HandleErr just plain wasn't there. Indeed, all on error goto <handler_name> statements in every function in the stack are ignored when ThisWorkbook.Workbook_Open is called.

What the heck??

[TMS]

The first End Sub should be Exit Sub otherwise the label is outside the procedure

Please Login or Register  to view this content.

Other than that, it works just fine.

[XmisterIS]

ah! Yes, sorry, that was an entirely innocent typo on my part when I was typing the code in the original message (corrected now). I still observe the behaviour I originally described in Workbook_Open, even with the file you have provided.

[TMS]

Maybe you need to check your security settings and that you enable macros when you open the workbook.

The code works just fine. I copied your coded, edited the typo and put it in that workbook. Saved it, closed it and re-opened it. Displays message.


Regards, TMS

[XmisterIS]

Thanks, I will check, although I do believe I have macros enabled. At least we have ruled out VBA as the source of the problem.

[TMS]

You're welcome. Thanks for the rep.

[Izandol]

I think perhaps you have 'Break on all errors' set in your VB Editor options?

[XmisterIS]

Just checked that; I have "break on unhandled errors" set.

[Izandol]

Do you open the workbook with code or within Excel?

[XmisterIS]

What do you mean?

[Izandol]

I mean do you open this workbook using Workbooks.open in vba or manually File - Open?

[XmisterIS]

Oic ... I open it manually.

[Izandol]

Is it same if you open Excel in Safe Mode and then open workbook TMS provided?

[XmisterIS]

Now here's a funny thing ... my laptop did an automatic update upon shutdown yesterday, and when I spark it up this morning ... bingo! Everything works fine (i.e. the problem is no longer there). So it was fixed by a windows update. Possibly something in excel got corrupted. Who knows!