Ribbon Change based on Non-Ribbon Object

[diakonos1984]

I am trying to build a dynamic ribbon in Excel 2007. I can find lots of examples on the web of ribbon buttons that change image when the button is pressed (essentially they toggle). However, I want to change the button based on the contents of a particular workbook. I already have a function that checks anytime a workbook is activated to see if that workbook is of what we will call "SpecialType".

It looks something like:

Please Login or Register  to view this content.

My ribbon code looks something like:

Please Login or Register  to view this content.

I have followed the directions for changing labels (close enough) on several websites: http://www.rondebruin.nl/win/s2/win009.htm or http://gregmaxey.mvps.org/word_tip_p...bbon_main.html
The problem is they all initiate the image change by something else in the ribbon and so cleanly pass the ribbon id around. I need to initiate by my xlApp_WorkbookActivate and then set the image in the ribbon.

They (and I here) have a GetImage function that requires the IRibbonControl, and since I haven't pressed a button on the ribbon, I don't have it! (I think...)

Please Login or Register  to view this content.

Basically, I have all the pieces from these other websites, but since my application is slightly different, I can't figure out how to tie the pieces together!

This guy ALMOST solved it for me in Question 3 on http://excelusergroup.org/blogs/nick...he-ribbon.aspx
Unfortunately, he only sets the image initially. He was asked in the 5th comment how to update the ribbon when a change occurred, but he didn't answer. If someone could even answer the question the way it was asked in that blog, I could probably figure out how to apply it to my need!

Thanks!

[diakonos1984]

Anyone? This should be easy! For anyone but me...

[Andy Pope]

You have used the onLoad method in the xml (ribbonLoaded), so within that VBA event routine you can store a reference to the ribbon object.

The in the code xlApp_WorkbookActivate if correct workbook you can use the Invalidate method to update the ribbon.

[romperstomper]

Your workbookactivate event can use the stored Ribbon object from your onLoad callback to invalidate the control in question. Your GetImage callback should then simply return whichever image is appropriate.

[diakonos1984]

Thanks, guys! So my RibbonLoaded sub looks like this:

Please Login or Register  to view this content.

If I modify my xlApp_WorkbookActivate sub

Please Login or Register  to view this content.

I get the Run-time error '91': Object variable of With block variable not set on the myRibbon.Invalidate line.

I'm thinking some of this may have to to with where these subs live?
xlApp_WorkbookActivate is in a separate Class module.
RibbonLoaded and GetImage are in a standard module.
All of it is in an .xlam add-in.

Thanks again for your help!

[romperstomper]

Try reopening the workbook. Changing the code will have reset the myRibbon variable. (see here: http://www.rondebruin.nl/win/s2/win015.htm)

[diakonos1984]

I shut Excel down entirely and reopened it. On opening the blank Book1 automatically (as Excel always does on program start) I got the Run-time error '91': Object variable of With block variable not set on the myRibbon.Invalidate line. My immediate window does NOT show the "Ribbon Loaded" from my Debug.Print command in the RibbonLoaded sub.

Is it possible that the xlApp_WorkbookActivate sub is running before the RibbonLoaded sub? How do I get it to wait?

[romperstomper]

No because the add-in should be loading the ribbon and that will occur before Book1 is opened. Can you zip and post the add-in for testing?

[diakonos1984]

All right, here it is. You will quickly notice that I had sanitized my code in earlier posts.

This is a tool for creating specialized reports from Quickbooks (our accounting software).

When you install the add-in, you will have a new ribbon called "Quickbooks Tools". In the first group are 6 buttons. The top left button is the one I am working with. If the active workbook is a "Cost-to-Date" sheet (like the template spreadsheet included in the zip file) then the button should display a checkmark. If the the active workbook is NOT a "Cost-to-Date" sheet (qbfCostToDateTest is the sub that performs this test), then the button should display a "1".

I haven't played with the rest of the add-in in a long time, so I am not sure how much else of it works.

Thanks for your help!

Add-in for troubleshooting.zip

[romperstomper]

OK - change the Workbook_Open code to:

Please Login or Register  to view this content.

then add this routine to the GUI module:

Please Login or Register  to view this content.

and it should be fine.

[diakonos1984]

Hmmmm... I tried adding as you said, still get the same error. So it actually worked on your end?!?!

A couple things I found, may help...

I already had a routine similar to your Sub Startup in ThisWorkbook. It looks like this:

Please Login or Register  to view this content.

I threw yours in too, and also one with the syntax already generated in my Class1 module:

Please Login or Register  to view this content.

I added some more Debug.Prints throughout so I could see what is actually happening. When I close down Excel, and re-open it, I immediately get the same Run-time error '91': Object variable of With block variable not set on the myRibbon.Invalidate line.
When I look in the immediate window, I see:

Workbook_Open sub from ThisWorkbook is running
xlApp_WorkbookOpen sub from Class1 is running
Workbook_Open sub from ThisWorkbook is running
xlApp_WorkbookOpen sub from Class1 is running
xlApp_WorkbookOpen sub from Class1 is running
Book1 Cost-To-Date test = False
Book1 Cost-To-Date test = False

This tells me that my pre-existing Workbook_Open sub gets run. The one I added to my Class1 module with syntax generated by the modeul gets run, but your Workbook_Open sub doesn't. Apparently neither one actually calls the GUI.Startup routine, because I never see "Set" in the immediate window.

Do I need to get rid of some of my similar subs? Is the computer getting confused and running mine instead of yours?

Any ideas?

[diakonos1984]

I thought it might be worth describing EXACTLY what is going on when Excel is started:

1) Run-time error '91': Object variable of With block variable not set
1a) I can see that no custom ribbons are loaded, and the default values in some of the built-in ribbons are blank. Eg, font type and size are blank, styles gallery is empty.
1b) I can see that a workbook (Book1) is loaded and apparently active.

2) I click debug on the error popup. The VBA window pops up and immediately gives me a message box "Can't execute in break mode"
2a) The styles gallery has progress indicators
2b) One of my other add-in ribbons is listed in the ribbon bar
2c) The immediate window shows

Workbook_Open sub from ThisWorkbook is running
Workbook_Open sub from ThisWorkbook is running
Book1 Cost-To-Date test = False
Book1 Cost-To-Date test = False

3) I click OK on the message box. The VBA window immediately gives me an other message box "Can't execute in break mode".
3a) Nothing in the UI or immediate window has changed.

4) I click OK on the message box.
4a) Now my code shows in break mode with the yellow arrow at the myRibbon.Invalidate line where it always has this error.
4b) My add-in ribbon now shows up in ribbon bar.

5) I click on my ribbon. The VBA window immediately gives me an other message box "Can't execute in break mode".
5a) No changes show up in the UI or the immediate window.

6) I click OK on the message box.
6a) My ribbon shows up and we are still in break mode, now just waiting.

Does that not perhaps look like it's trying to call the ribbon before it is ready?

[romperstomper]

I think you misunderstood. This:

Please Login or Register  to view this content.

replaces the Workbook_Open code you have in the ThisWorkbook module of your add-in. It should not be turned into xlApp_WorkbookOpen.

[diakonos1984]

Oh yeah! That's fantastic! You cleared it all up for me... now if only I understood WHY that works. Location of these subs and their significance is presently over my head.