MS-Excel / General Formatting

---

Getting and Changing Control Values

To round out your RibbonX interface, you'll want at least some of the controls to interact with each other. For example, when you activate a particular check box, you might want this to also disable a certain button. Similarly, if the user selects or enters a value in a combo box, you might want to use that value in a procedure.

The bad news is that when it comes to determining the current state or value of a control, you're on your own. That is, RibbonX does not offer any properties or methods by which you can work with custom Ribbon controls directly. The good news is that it's not difficult to provide your code with limited access to the current state or value of a control.

The trick to all this is to establish module-level variables that hold the state or value of your controls. For a check box or toggle button, use a Boolean variable that you set to True when the control is checked or pressed, and that you set to False when the control is unchecked or unpressed. You initialize this variable in the getPressed callback macro, and you update the variable in the onAction callback macro. Here's some code that declares a module-level Boolean variable named booDeveloperTools, and then shows a toggle button's getPressed and onAction callback macros, both of which change the value of booDeveloperTools:

Private booDeveloperTools As Boolean
Sub tbToggleDeveloperTools_GetPressed(ByVal control As IRibbonControl, ByRef returnVal)
    returnVal = Options.ShowDevTools
    booDeveloperTools = returnVal
End Sub

Sub tbToggleDeveloperTools_OnAction(ByVal control As IRibbonControl, pressed As Boolean)
    Options.ShowDevTools = pressed
    booDeveloperTools = pressed
End Sub

For a combo box, use a String variable that stores the value entered or selected by the user. Use the getText callback macro to initialize the variable, and use the onChange callback macro to update the variable. In the combo box example, you can declare a module-level named strSelectedWindow and update the callback macros as follows:

Private strSelectedWindow As String
Sub cbWindows_GetText(ByVal control As IRibbonControl, ByRef returnVal)
    returnVal = "Select a window..."
    strSelectedWindow = ""
End Sub

Sub cbWindows_OnChange(ByVal control As IRibbonControl, text As String)
    strSelectedWindow = text
End Sub

Notice that in this case strSelectedWindow is initially set to the null string because at first no window is selected.

Because these new variables are module-level, you can them use them in any other procedure in the module. For example, suppose you want to take the currently selected window in the combo box and close it. One way to accomplish this would be to add a new button to the Ribbon, and you want to set up that button so that it closes the selected window. Here's the XML for the new button:

<button id="btnCloseWindow"
    label="Close Window"
    imageMso="FileClose"
    onAction="Module1.btnCloseWindow_OnAction" />

Here's the btnCloseWindow_OnAction macro:

Sub btnCloseWindow_OnAction(ByVal control As IRibbonControl)
    If strSelectedWindow <> "" Then
	Windows(strSelectedWindow).Close
	myRibbon.InvalidateControl "combobox1"
    End If
End Sub

The code makes sure that strSelectedWindow isn't the null string, and then runs the Close method on the window name stored in strSelectedWindow. Because closing an open window puts the window list out of date, the code also runs InvalidateControl on the combo box to reset it.

The btnCloseWindow_OnAction code checks strSelectedWindow to make sure it's not null. However, a properly constructed interface would disable the Close Window button while not window is selected. To handle this, you must first add a getEnabled attribute to the Close Window button:

<button id="btnCloseWindow"
	label="Close Window"
	imageMso="FileClose"
	getEnabled="Module1.btnCloseWindow_GetEnabled"
	onAction="Module1.btnCloseWindow_OnAction" />

Here's the callback macro:

Sub btnCloseWindow_GetEnabled(ByVal control As IRibbonControl, ByRef returnVal)
    If strSelectedWindow = "" Then
	returnVal = False
    Else
	returnVal = True
    End If
End Sub

If strSelectedWindow is the null string, the macro returns False and the button is disabled; otherwise it returns True and the button is enabled.

The enabled state of the button also needs to be checked when the combo box changes and when we refresh the list. Here are the corresponding macros updated to run the InvalidateControl method on the btnCloseWindow element:

Sub cbWindows_OnChange(ByVal control As IRibbonControl, text As String)
    strSelectedWindow = text
    myRibbon.InvalidateControl "btnCloseWindow"
End Sub

Sub btnRefreshList_OnAction(ByVal control As IRibbonControl)
    myRibbon.InvalidateControl "cbWindows"
    myRibbon.InvalidateControl "btnCloseWindow"
End Sub