Feeds:
Posts
Comments

Archive for the ‘Excel Quirks/Problems’ Category

I have experienced this problem myself from time to time. For some unknown reason, double-clicking an Excel file (.xls, .xlsx, etc.) in Windows Explorer (My Computer, whatever)  does not open the file. It opens Excel with a blank workbook, but the workbook you clicked on is not loaded. If you leave Excel open and double-click the file again, it will open just fine. If, however, you quit Excel completely, then double-click the file again… you’re back to the original problem.

After much investigation and aggravation, I’ve finally decided upon a solution that fixes this problem… although I really can’t say for sure why it fixes the problem!

First of all, let me say that I’m a total Excel minimalist. I will not load an add-in, DLL, or module that does not have an immediate purpose for existing. I never attach the “Analysis Toolpak” or “Solver” add-ins unless I know I’ll be using them (and I do use them occasionally). After I’m done using them, I detach them. Having said that, it appears that this lack of add-ins may be part of the problem.

The solution I’ve found is to open Excel and attach one of the common add-ins (I usually use Solver), then quit Excel. After that, the problem miraculously goes away!  I can go back and detach the add-in, and the problem is still resolved.  Go figure.

Six months later, the problem resurfaced again. No idea why.  So, I just detached all my add-ins. Quit Excel. Restarted Excel, re-attached the Solver Add-in. Quit and restarted Excel one more time. Problem solved again.

—————————————————–

As an additional service, let me review the solutions that did NOT work, because you’ll problem come across these while web surfing for solutions:
 
1. De-select the “Ignore other applications” option on the “General” options tab – One of the “official” Microsoft knowledge base solutions. While this may work under certain circumstances, it was absolutely useless to me because I didn’t have this option selected to begin with.
 
2. Excel /unregserver, then /regserver – Another option that I find useful on occasion, when needing to reset command bars and menus back to default condition. But for this particular problem… useless.
 
3. File Association settings – In Windows Explorer, right click an Excel workbook file and select “Open With… Choose Program…” In the next dialog, pick the Excel program and check off “Always use the selected program to open this kind of file”. This solution was concise, logical, and seemed a perfect way to resolve the exact problem I was having…   Nope, didn’t work. On to the next one…
 
4. Mess with the “File Types” settings in Windows Explorer (Tools… Options… File Types tab) – This is the first one that attracted my attention because it seemed so coherent and the posting had dozens of replies saying thanks for the wonderful solution, it worked like a charm, etc. Well, my advice is to steer well clear of this one unless you really know what you’re doing. This solution requires you to make changes to the Registered File Types settings so, being a careful guy, I made detailed notes of the current settings and then implemented the new recommended settings. It fixed the problem, but created a new one. Now it was opening the file on double-click, but then (I think) trying to open a second instance of Excel and, finally, causing Excel to crash! So, I went back in to the File Type settings and put everything back just the way it was, but it didn’t fix the Excel crashing problem. It took another hour of research and manual registry editing, to get it back to its original state.  Avoid this solution. It’s not worth the aggravation.
Advertisements

Read Full Post »

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 »