I have, via the blog contact form received three questions from Milan, these were
-
Can you remove the Office Button?
-
Can you link a combobox (on the ribbon) to a cell/range?
-
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