+ Reply to Thread
Results 1 to 11 of 11

Excel Custom RibbonX Label updated from VBA

  1. #1
    Forum Contributor
    Join Date
    12-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    201

    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?

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel Custom RibbonX Label updated from VBA

    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.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Excel Custom RibbonX Label updated from VBA

    Trouble is the Invalidate will not execute multiple times within the loop.

    Best use the Status bar to display information.

    And what exactly are you doing that required days to run?
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    12-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Excel Custom RibbonX Label updated from VBA

    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.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Excel Custom RibbonX Label updated from VBA

    Did you check my example?

    As Izandol said you need to use the onLoad callback.

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rbx_onLoad">
    Please Login or Register  to view this content.
    But you will see if you run the Test routine that although the msgboxes are displayed the label only actually updates on completion of macro.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Excel Custom RibbonX Label updated from VBA

    And just to clarify, you code doesn't take days to run it processes days, correct?

  7. #7
    Forum Contributor
    Join Date
    12-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Excel Custom RibbonX Label updated from VBA

    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.

  8. #8
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel Custom RibbonX Label updated from VBA

    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.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Excel Custom RibbonX Label updated from VBA

    @ Izandol , am I missing something. when I run StartLoop it only executes once. It does not actually loop. So in order to display more than "Loop 0" I have to run Startloop again.

  10. #10
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel Custom RibbonX Label updated from VBA

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

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Excel Custom RibbonX Label updated from VBA

    I had to add a second to the onTime call in order to see it update

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2007 : How to delete custom number for data label?
    By willow2008 in forum Excel General
    Replies: 0
    Last Post: 06-25-2010, 07:39 PM
  2. Custom Label
    By Bryansb in forum Excel General
    Replies: 3
    Last Post: 09-26-2006, 12:19 AM
  3. Help with a custom label?
    By wishmyr in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-24-2006, 12:50 PM
  4. [SOLVED] custom label
    By wishmyr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2006, 10:25 AM
  5. [SOLVED] Adding a custom label to a series point
    By den1s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2006, 09:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1