How to preserve or regain the Id of my custom ribbon UI?

[WernerGg]

I have a little Excel 2007 application with a standard ribbon UI. See the CustomUI.xml and the VBA-code below.

I have uploaded an Excel 2007 TestRibbonUI.xlsm to box.net from where you can get it with the link http://www.box.net/shared/8uznug7s3r

My new tab with name "My Tab" and id="tabCustom" has a group "grpToggle" with two buttons "btn1" and "btn2". Their enabled-states shall be controlled at runtime. Pressing any of them calles "DoButton" which just toggles both states.

The problem is that the UI is initialized only once during load. To change enabled-state or visibility, controls must be forced to reinitialize. For that we have to store the UIs Id during load by means of the o n L o a d -callback (sorry, the vBulletin-software replaces onL... with asterix. God knows why. Is this a dirty word?)

But this Id can only be stored in a static variable (Private guiRibbon As IRibbonUI), which gets lost after errors or during reset from the VBA-IDE.

The third button "Force Error" in our group just produces a zero division. After that the ribbon UI does no longer work and the workbook must be closed and reopen by the user.

Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?

In the VBA help we can find an example which uses a method "guiRibbon.Refresh". I think this is exactly what we would need instead of the current ribbon UI design which is more than weak in that point. Unfortunatly that Refresh is not implemented.

Sorry I am not able to enter the xml-code and not the VBA code within code-tags. The vBulletin software always corrupts the things. Stupid!

 

[hatman]

Yes, actually I do use the same routine name in all of my applications. But the people who have reported this problem have none of my other addins or applications loaded... I assume you are going to suggest that I rename the procedure, in order to prevent the possibility of a cross-call. It wouldn;t hurt.

 

[RoryA]

Yep - see Ken's blog here.

 

[hatman]

Ahh, I seem to remember stumblng upon this article a couple of years ago, when I had a conflict with applications built by a co-worker. Thanx for pointing me back to it.

I'll make the routine name change, and also working on some more graceful error handling, for the belt and suspenders approach...

 

[hatman]

Hmm, looks like I already changed the name of this so it is already unique. I suppose that someone could have stolen my code since I made the name change last year, thus creating the conflict I was trying to avoid when I originally changed the name. I'm not 100% convinced this is the root cause... but combined with my other fixes, I think this'll put the last nail in the coffin.

Thanx Rory.

 

[RoryA]

No worries. Please post back if you find out the problem.

 

[hatman]

I just stumbled on this again. On my own machine for the first time. It seems that the ****** routine was called and worked fine. But after having this file open on my machine for several hours, and swapping back and forth to other workbooks in the same application session, suddenly my error handler triggered. On closer investigation, the global named range where I was storing the pointer had a value of ZERO. I am still trying to figure out why. I have exactly (2) lines of code in the entire project that reset this to ZERO. 1) In an admin routine that I run before I dsitribute new versions of the file which clears all data and resets all workbook resdient settings to nominal, and 2) In the workbook open event, to prevent my error handler from triggering before the ribbon loads, in case I forgot to run the admin routine to clear all values before closing the workbook. The admin routine did not get tiggered, because none of the other values in the file had been reset. I don;t see how the workbook open event got triggered.

So now I see that the pointer was properly captured and stored in the ****** event... but am perplexed how this global named range got set to a value of ZERO at some later time. The investigation continues.

 

[Jaafar Tribak]

I just stumbled on this again. On my own machine for the first time. It seems that the ****** routine was called and worked fine. But after having this file open on my machine for several hours, and swapping back and forth to other workbooks in the same application session, suddenly my error handler triggered. On closer investigation, the global named range where I was storing the pointer had a value of ZERO. I am still trying to figure out why. I have exactly (2) lines of code in the entire project that reset this to ZERO. 1) In an admin routine that I run before I dsitribute new versions of the file which clears all data and resets all workbook resdient settings to nominal, and 2) In the workbook open event, to prevent my error handler from triggering before the ribbon loads, in case I forgot to run the admin routine to clear all values before closing the workbook. The admin routine did not get tiggered, because none of the other values in the file had been reset. I don;t see how the workbook open event got triggered.

So now I see that the pointer was properly captured and stored in the ****** event... but am perplexed how this global named range got set to a value of ZERO at some later time. The investigation continues.

Just taking a wild guess at this. Try storing the pointer in other than a name like in a cell , the registry, a window Property... etc and see if it resolves the issue.

 

[hatman]

Thanx Jaafar. I thought of that. I'm not sure what differnce it will make, but I agree, it's worth a shot, considering that there really aren't a lot of code changes required.

Another thought I had was to reduce my "opportunities" by half and remove the line of code that resets the value to ZERO in the Open event. It's really there to avoid the occassional nuisance cases during developement where I save the workbook with data in it, close it and re-open it with the OLD ribbon pointer still persisting from the previous session.

It'll be a few weeks before I distribute the next version of the application... so I won;t be able to evaluate any of this for a while...

 

[hatman]

So I distributed new code last week. Included is a routine that logs errors to a central log file. I have had 6 occurances of the error, always in the same sub-routine. It would be nice if there was runtieme access to the Call Stack using VBA code, so I could get a sens of how the code execution arrived at this point for each error. But at least the search is being focused. The dilemma goes on.

 

[chazz]

been hunting all morning for a clue why attempting to invalidate a Custom UI gives me "object variable or with block variable blah-de-blah-blah".

boy do I feel like an amateur reading this.