Archive for March, 2010

I have an Excel workbook application (Excel 2003) that contains a hidden worksheet which functions as a template. When the users makes a request, the template worksheet is populated with data, assigned a generic name, and copied to a new, visible worksheet within the workbook. The template contains ActiveX Command Buttons and VBA code to manage the button click events. If a worksheet of the same generic name already exists, it is deleted  and then replaced with the new one.

The whole process was working fine until I added the Command Buttons to the worksheet. After that, every time I tried to clone the template, I would get a vicious VBA error “Cannot Enter Break Mode at this Time”. Not only did this cause my subroutine to error out without completing, but it appeared to reset all the global variables in my project! The next time I would try to access a global collection, which I knew was properly initialized, I would get anther error telling me that the object had not been intialized (1004 error, I think).

I could not even step through the code successfully. Upon hitting the line of code which deleted the existing worksheet, I would see the aforementioned error and then my Execution Point highlight was just gone…  as if the code abnormally terminated, without raising any other errors.

I spent quite a bit of time searching the web for answers to this problem and, while I found some interesting technical explanations of what this error means, I did not find any workable solutions. But, to make a long story short, I finally figured out that it was the ActiveX Command Buttons, and replaced them with the Forms Command Buttons (which I’m not really partial to, but what choice did I have?), and the problem went away.  Just like that.

Now, of course, I had to move my button click code into a standard module so that it was accessible to all the worksheets cloned from the template as well, and change a bunch of other things to accommodate. But, it solved the problem, and that’s the main thing. If anyone has a good explanation as to the origins of this problem, I’d love to hear from you.


Read Full Post »