Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Questions on customising the ribbon

I have, via the blog contact form received three questions from Milan, these were

  1. Can you remove the Office Button?
  2. Can you link a combobox (on the ribbon) to a cell/range?
  3. Can you link a label (on the ribbon) to a cell?

The answer to No.1 was answered in the 2nd of my series on customising the ribbon. In the section under Full Dictator Application.

No. 2 is yes you very much can but it is not a simple process like having a 'link' property or similar. The write-up below demonstrates how you would take a range of cells (BoxList @ $A$!:$A$10) to fill the combobox and then display the selected item in a cell on the worksheet ($D$1).

A comboBox has a DropDown (so is similar to a dropDown ribbon control), but allows extra entries to be typed in at runtime.

The first thing we need to do is set up the RibbonX in the file (for detail, see here). For this demo I am setting startFromScratch="true", so all we should have from the below RibbonX is a single tab, with one group and one comboBox. Notice too, that at this stage the comboBox is empty. This is because we will be building this when the workbook opens, using a Workbook_Open() event

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" 
                            onChange="cmb_onChange"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Notice that we are using four 'callback' attributes to populate the comboBox and to 'link' it to a cell. These are used for the following reasons:

  • getItemID() = Used to 'set' the unique ID necessary for every control on the ribbon (It is tough to think that the names 'getxxx' are actually better though of as 'set'. It is meaning the Ribbon needs to 'get' rather than you need to 'set' if that makes sense?)
  • getItemLabel() = Used to 'set' the text of each item displayed in the control
  • getItemCount() = Used to 'set' the number of items the control should expect to display.
  • onChange()= Used to provide the 'link' to a cell on the worksheet. This fires each time the control is changed.

Next we generate the callback signatures for these by pressing the button in the Custom UI Editor. You will notice below that the UI editor does not generate a getItemID callback. this is a bug, the signature is given below to help you.

'Callback for Combobox1 getItemID (NOT GIVEN BY CALLBACK GENERATOR)
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
End Sub

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
End Sub

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
End Sub

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
End Sub

We will use a Workbook_Open() event to build the array of items to load into the control. This event, as the name implies, fires when the workbook is opened. It is placed in the 'ThisWorkbook" module.

Option Explicit
'There are two global variables at the top of a standard module
'iItemcount and vRngValues

Private Sub Workbook_Open()
'Counter variable
Dim x As Integer
'Counts the cells in the range, used later to set no. of Items in control.
iItemcount = Worksheets("Sheet1").Range("BoxList").Cells.Count - 1
'Re-Dimension the dynamic array to it's actual size.
ReDim vRngValues(iItemcount)
'Build the array
For x = 0 To iItemcount
    vRngValues(x) = Worksheets("Sheet1").Cells(1 + x, 1).Value
Next x
End Sub

Now we will have the range 'BoxList' assigned to a one-dimensional array and the count of items, in public variables available to all code in the project. (remember this for later)

First let's advise the control how many items we will need in the control, using the global integer variable iItemcount (built in the Workbook_Open() event) and the getItemCount() callback, as below. (The comments explain what the procedure does, in fact we are using just one line of code!). These callbacks are all placed in a standard module.

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
'Use the global variable collected in the Workbook_Open()
'assigning it to the returnedVal parameter
    returnedVal = iItemcount
End Sub

Next, as each item in the control (and the control itself for that matter) need a unique ID we will build this next using the getItemID callback, as below. Again the comments explain what is happening.

'Callback for Combobox1 getItemID
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
'Set the ID parameter in the callback signature to each 'index'
'in the signature. We are adding cmbBox text to make it unique
'as using just a number may clash with other controls/items
'indexes in controls are zero based
    ID = "cmbBox" & index
End Sub

Next we will write the last piece of code to actually build it (the fourth will be used as we use the control). This is the getItemLabel callback (below) that 'sets' the actual text that will be displayed in the combobox. (Again, just one line of code!)

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'Sets the returnVal parameter, which carries the text back to the control
'This fires for each control 'item', so the 'index' parameter
'provides a zero based index for the current item, which we use in the code
    returnedVal = vRngValues(index)
End Sub

Lastly, we will use the onChange() callback to assign the selected option from the comboBox, to a cell (D1) on our worksheet.

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
'Set the 'text' parameter to a cell value
    Worksheets("Sheet1").Range("D1").Value = text
End Sub

That's all you need, job done! The image below shows the result.

Lastly, question No.3. I am hoping that the questioner was referring to the labelControl. This is used simply as a textual control that can act as a label to anything else on a group. If so then yes, all we need to do is set the control's getLabel attribute using it's callback. I have added the ribbonX to the previous example, (it's just one line) and the callback, added to a standard module is below that, which picks up the value from D2.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" onChange="cmb_onChange"/>
                    <labelControl id="myLabel" getLabel="lbl_getLabel"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The getLabel VBA callback

'Callback for myLabel getLabel
Sub lbl_getLabel(control As IRibbonControl, ByRef returnedVal)
'Assigns the value in cell D2 to the returnVal parameter
    returnedVal = Worksheets("Sheet1").Range("D2").Value
End Sub

The resulting label from cell D2

Hopefully this goes someway to answering the OP questions and helps to further everyone's understanding of customising the Ribbon in Office 2007.

More to come.


Posted Mar 02 2008, 10:52 AM by Nick Hodge

Comments

Harlan Grove wrote re: Questions on customising the ribbon
on Sun, Mar 2 2008 9:38 PM

Didn't get the original e-mails, but question 1 as you pose it above could be interpreted to mean whether it's possible to remove the office button while leaving the ribbon.

As for the rest, you really find this easier than the classic UI?

Nick Hodge wrote re: Questions on customising the ribbon
on Mon, Mar 3 2008 3:24 AM

Harlan

The original email was from the contact form, so only I got it, that's why I was trying to get them back out in public.

You cannot remove the Office button in RibbonX yet, as I suspect you know, so it is not possible to remove it and keep the ribbon. I am devising some further notes to remove, disable or re-purpose the functions on the office menu, which may bring the OP a bit closer.

On the point of the classic UI (where did 'classic' come from?). personally yes. It takes a lot to write it up in a blog post, but I have a few useful templates set up in my UI editor, which I single-click into place, copy the generated callbacks and just write the code in between.

I am confident that when the workbook is closed or the add-in removed, the ribbon will be just as it was before I took over.

Perfect it very much isn't, but it also isn't tough. Given what we currently have to work with, it isn't a bad V1.0 (IMO)

Milan wrote re: Questions on customising the ribbon
on Mon, Mar 3 2008 5:07 AM

How can we declare the width (sizeString) of the comboBox in this example? Or is it only option for static attribute.

Nick Hodge wrote re: Questions on customising the ribbon
on Mon, Mar 3 2008 7:23 AM

Milan

Yes, currently sizeString is only a static attribute, maybe next release?

Milan wrote re: Questions on customising the ribbon
on Sun, Mar 16 2008 1:14 PM

Regarding question No.3.

Can the label be updated when the value of cell D2 changes.

Kate wrote re: Questions on customising the ribbon
on Tue, Apr 15 2008 11:47 PM

Hi, Thanks for sharing all the useful information/codes. I've been trying to play around ribbon(for example: command idMso="SheetProtect" enabled="false"...). It seems like this kind of command can only works in one workbook. When I tried to open another workbook, the protection ribbon shows up as enabled again...any idea/suggestion?

Thanks in advance!

Nick Hodge wrote re: Questions on customising the ribbon
on Wed, Apr 16 2008 12:33 AM

Kate

The customisation you have is in a workbook, so it will only apply while that workbook is loaded.

If you want it available to all workbooks then load it as an add-in (xlam) or in your personal.xlsm file

Let me know if you want help with that

pk wrote re: Questions on customising the ribbon
on Mon, Apr 21 2008 8:32 AM

So the way to make code available to all workbooks is through an add-in (xlam), Nick, can you do a page on how to create, implement, and distribute an xlam?

Thanks so much for all the time you've put into this it has been immensely helpful.

Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.