Going Toolbar Nuts! (Excel 2000, XP)

[kmonaco]

Okay, this has been driving me nuts for a couple months now. I finally gave up the struggle and worked on other things, but now I'm back to this workbook, and I need to get it figured out! Here's the deal. This is a fairly large workbook, even without data, which depends on an even more elaborate custom toolbar (commandbar). My problem is that I can't get any of my modifications to "stick." I'll add several new items, or move items from one menu to another, or change an image--whatever--and go through all the steps of deleting the toolbar from the workbook, attaching the changed one from the workspace, and then deleting the one from the workspace when closing the workbook. But the changes are gone when I open the workbook again. Most of the time. Every once in awhile, I'll think I'll have discovered the secret. But then, the next time I open the workbook, the new items will be missing again.

Sometimes, after three or four attempts at making changes, the toolbar that DIDN'T appear several tries ago will suddenly show up, with the original changes I made and none of the recent ones. Or it will show up with stuff missing that I didn't delete. Since I quit work on it, it has been stable, but once I start to make changes, it gets flaky again.

I thought, of course, that I must be missing something. I work primarily in Word, where toolbars are always part of the template and generally behave themselves. So I printed out three different sets of "how to" instructions dealing with the appropriate way to deal with toolbars in Excel. I followed each set, to the letter, over and over again. Same problem.

I tried renaming the workspace files. And half a dozen other desperate "won't hurt to try" techniques. For about eight hours one Saturday, back when we thought this project was only going to take two weeks, and I was frantic to get it done by Monday. Nothing. I tried copying the toolbar to a new workbook, in case my workbook was corrupted in some way. No deal.

So, on that Monday way back in June, I pulled in the other programmer, gave him the instructions, and stepped him through the process, while he double-checked me on the instructions. He couldn't figure it out. So I pulled in one of the IT guys. I had him try to make a change and get it to stick. And HE couldn't get it to work. And these are both really bright guys. REALLY bright, with the IT guy especially good at troubleshooting problems just like this. No luck.

I tried moving from Excel 2000 to XP. Nope. To Excel 2003. Nope.

I tried various combinations of deleting and/or not deleting the toolbar from the workspace when closing the document. Nope. Sometimes, not only would the changes not be there, but stuff that HAD been there would suddenly be missing. I recreated the same items so many times, I finally printed out images of them the way they were at the latest "good" point so that I could duplicate them more easily. It was driving me CRAZY!!!

I searched high and low, and had the IT guys search, too, for someone else who had the same problem. Nothing. As I've said, I've gone on to work on the other parts of the programming without modifying the toolbar, but I'm nearly to the point where I'm either going to have to figure this out or come up with another whole scheme for how the workbook will function.

So, WHAT CAN I TRY NEXT???? If you need to see the file, I can send it to an individual, but I don't want to post it. And even zipped, it is 136k.

Help, please!

--Karyl

[HansV]

I don't have the slightest idea what workspace files (.xlw) have to do with toolbars.

The essential point in saving a toolbar with a workbook is to save the workbook AFTER attaching the custom toolbar to the workbook, but BEFORE you close it. And you must have code in the Workbook_BeforeClose event to delete the toolbar when the workbook is closed.

[kmonaco]

According to the Visual Basic Programmers Guide for Office 2000, "Excel lets you store command bars with an individual workbook or in the Excel workspace. Workspace command bars are saved in a file named Excel.xlb. . . . When you open a workbook, Excel copies the workbook's custom command bars that do not already exist in the workspace to the workspace. These copied command bars are not deleted from the workspace when you close your workbook. If you want custom command bars to be available only when your workbook is open, you must programmatically delete them from the workspace when your workbook closes. "

I was desperate, so in an attempt to troubleshoot, I renamed the xlb files to try to clear out the old toolbars that kept showing up. Didn't help.

I've saved the workbook after attaching the custom toolbar in every combination of ways you can think of, even to doing just one step at a time and saving as I went along. Delete toolbar from workbook, close the customize dialog, save, open customize, copy to workbook, close customize, save, open customize, delete toolbar from workspace manually, close customize, save. And the same process using code to delete the toolbar. And a dozen variations thereof, with added steps and skipped steps and combined steps, trying to find the right magic process. And when I thought I had a process that finally worked (documented, because I eventually started writing down my steps), then it wouldn't work the next time. And the stuff that HAD worked would often be gone again the next time I opened the workbook, although it had been right the time before. Or the new stuff PLUS a bunch of the old stuff would be gone, and I'd have to start over. And a few open and closes later, the new changes wouldn't be there, but the changes I'd made four times ago would suddenly show up, out of nowhere.

It is very, very, very strange. . .

--Karyl

[HansV]

I'm sorry, I can't explain that either. I would start with a new blank workbook, and a simple custom toolbar with just one or two buttons. See if you can make it work consistently there.

[unkamunka]

Two questions:<UL><LI> Do you have any VBA code running other than the toolbar code? If so, do you still have any problems if you comment out all the non-toolbar code in the workbook - as well as unloading add-ins, personal.xls etc etc<LI>In a slightly similar vein, have you tried running the workbook away from the office network - preferably in a different location altogether?[/list]HTH

[kmonaco]

After much trial and error, and a new problem with being unable to "Save As" without crashing out of Excel, I think I tracked the problem down to a corrupted form in the template. And maybe a corrupted toolbar as well. After removing all the code and forms from the workbook and still having toolbar problems, I eventually deleted the bad toolbar all together and confirmed several times that it was gone from Excel and from the workbook. Then I copied a completely new toolbar from another document. And, believe it or not, the OLD deleted toolbar showed up again instead of the new different one! Definitely something screwy with the workbook.

So, I moved everything to a new workbook, and when I brought over the new toolbar, it was okay. Except THEN I couldn't do a Save As. I tried removing one component at a time, and it finally turned out to be Form #7 (out of 7, of course) that was causing the problem. Once I got rid of that form (and replaced it with one from an earlier version of the template), things seemed to return somewhat normal behavior.

Thanks for all the suggestions. It helps to be able to look at it through someone else's eyes.

--Karyl

[unkamunka]

Good to hear that you've bottomed it. It's interesting to hear what it was.