Form Show method in event workbook_open in ThisWorkbook breaks on focus

Question

When I try to show userform1 with userform1.show in ThisWorkbook within the private sub workbook_open(), it does the strangest thing. (I'm using Excel 2007)- It enters break mode and stops the running of the code!!!

I open the macro enabled workbook and the userform appears as planned, but the when I move the mouse within the area of the userform it enters break mode and highlighting the row UserForm1.Show as if it is the problem.

Furthermore when I press F8 it's highlighting the private sub workbook_open() and another press highlights userform1.show again and another press shows me the run time error '400'.:

application-defined or object-defined error.

This has never happened to me before, I found a post that says it has no answer here.

Any ideas?

Answer 1

I also came across the same scenario.

What I did was to change the ShowModal property in the Form properties to False and the break went away.

Answer 2

I had the same problem, and the answers above didn't fix it. Restarting the PC resolved it temporarily, but after a while the problem returned. After a few frustrating days with a lot of restarting the pc, I came up with these conclusions:

What I think causes the problem is when you use ctrl+break to stop the code while running.

What solves the problem for me is to press ctrl+break while the code is NOT running, it'll show that it's in break-mode, and then press F5. It won't start running, but it will exit break mode again. When you've done this the problem should be fixed and you can run the code as usual. I guess this method works the same as restarting your pc, but it's a lot faster.

Answer 3

It's an odd behaviour I already faced some times when dev'ing... some tips that might help you out:

• Compile the code
• Clear all breakpoints
• Check project references (missing references may cause problems)
• Add some unnecessary statement (like if 1 = 2 then DoEvents) before the 'hidden breakpoint', compile the code and then remove the code again
• Install & run VBA Code Cleaner

Either way, that's a mystery for me the cause of this odd issue. It seems that somehow some breakpoints are kept in the memory even after removing them...

Hope it helps!

Answer 4

First thing to try is to recompile your VBA project and then save the workbook containing the macro. If compiling throws up an error you will probably get more information from the compiler message such as a missing reference.

Also make sure that you cleared all previously existing breakpoints. It might be the case that you first have to create a new breakpoint (F9) and then clear all breakpoints (Ctrl+Shift+F9) for the command to be enabled.