Excel Custom RibbonX Label updated from VBA

How do I update a label on a custom ribbon from VBA?

I added a label to a custom ribbon where I want to display a date. My model is set to run in a loop for a number of days. As it loops I want the date of the day it is running to be displayed on the custom ribbon. The book I have shows how to add the label control to the ribbon, shows a callback procedure called getLabel1 with the following code:

Please Login or Register  to view this content.

This will then display the system date on the ribbon but how do I go about to display a date of my own on the ribbon from within my loop in VBA? I don't know what to pass as parameters to the sub.

And can I add a icon of a calendar above the date well?

You must obtain reference to the IRibbonUI object in the onLoad callback for your CustomUI and then when you wish to change the date you may use the Invalidate or InvalidateControl methods of the object - the label will then run its callback. To pass the date you wish it is easier to use a Public or module level variable which is updated by your loop and then returned directly by the getLabel1 callback.

It's a "What If" model that is run from 2006 - today. It's made to run automatically one day after the other from start to end date. The statusbar is used to display where in the loop the program are (what it's doing at the moment) so displaying the date there can be done but will involve a lot of messages to be changed (which can be done if the ribbon doesn't work out).

I got the following from the book (it's located in a module):

Please Login or Register  to view this content.

Can use a module level public variable but how do I fire the Invalidate method? MyRibbon.Invalidate gives me an error 'Runtime error 91' - Object variable or With block variable not set.

Yes, it processes a number of days. It runs dates one after the other. Many dates/days in one run I can tell it to run any number of dates e.g. 10 dates or 100 dates but all in succession.

I see what you mean. The date only gets updated after completion of all the dates set

No way around it? Otherwise I'll just have to change my statusbar messages to include the run date.

Statusbar is easier for sure but you may try:

Please Login or Register  to view this content.

with Andy Pope's workbook. I cannot say for sure if this will work with your situation.

StartLoop is only to run once. Invalidating the control starts the loop within the getLabel1 callback which is run by the updateRibbon code.