This is a follow-up question to this one:
Excel Ribbon comboBox: always highlight selection
The following code allows you to select from three paper sizes using a comboBox. There is only only issue: the selected item doesn't stay highlighted.
@OlleSjögren writes, that you need the getSelectedItemIndex
-Callback to make this work.
Question: How do you build the getSelectedItemIndex-Callback?
If I add it to the XML, I get an error message: "the attribute is not declared".
Thanks!
' -- XML
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
<ribbon>
<tabs>
<tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">
<group id="GroupDemo2"
label="SelectPapersize"
imageMso="AddInManager">
<comboBox id="ComboBox001"
label="comboBox001"
getText="ComboBox001_GetText"
onChange="ComboBox001_OnChange">
<item id="Item_A3"
label="A3"/>
<item id="Item_A4"
label="A4"/>
<item id="Item_A5"
label="A5"/>
</comboBox>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
' -- Callback VBA in Module "RibbonCallbacks"
Option Explicit
Public RibUI As IRibbonUI
Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"
Sub LoadRibbon(Ribbon As IRibbonUI)
Set RibUI = Ribbon
RibUI.InvalidateControl "ComboBox001"
End Sub
'Callback for ComboBox001 onChange
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
Select Case id
Case "A3"
ActiveSheet.PageSetup.PaperSize = xlPaperA3
Case "A4"
ActiveSheet.PageSetup.PaperSize = xlPaperA4
Case "A5"
ActiveSheet.PageSetup.PaperSize = xlPaperA5
End Select
RibUI.InvalidateControl "ComboBox001"
SaveSetting myApp, mySett, myVal, id
End Sub
'Callback for ComboBox001 getText
Sub ComboBox001_getText(control As IRibbonControl, ByRef returnedVal)
Dim comboVal As String
comboVal = GetSetting(myApp, mySett, myVal, "No Value")
If comboVal <> "No Value" Then
returnedVal = comboVal
End If
End Sub
' -- VBA in "ThisWorkbook"
Private Sub Workbook_Open()
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim idx As String
Select Case PaperSize
Case "xlPaperA3"
idx = "A3"
Case "xlPaperA4"
idx = "A4"
Case "xlPaperA5"
idx = "A5"
End Select
SaveSetting myApp, mySett, myVal, idx
RibUI.InvalidateControl "ComboBox001"
End Sub
getSelectedItemIndex
is available for dropDown
instead of comboBox
.dropDown
.' --- XML
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
<ribbon>
<tabs>
<tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">
<group id="GroupDemo2"
label="SelectPapersize"
imageMso="AddInManager">
<dropDown id="DropDown1"
label="DropDown1"
onAction="DropDown1_onAction"
getSelectedItemIndex="DropDown1_GetSelectedItemIndex"
>
<item id="Item_A3"
label="A3"/>
<item id="Item_A4"
label="A4"/>
<item id="Item_A5"
label="A5"/>
</dropDown>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
' -- Stardard module
Option Explicit
Public RibUI As IRibbonUI
Sub LoadRibbon(ribbon As IRibbonUI)
Set RibUI = ribbon
RibUI.InvalidateControl "DropDown1"
End Sub
'Callback for DropDown1 onAction
Sub DropDown1_onAction(control As IRibbonControl, id As String, index As Integer)
Dim iSize As Long
Select Case Right(id, 2)
Case "A3"
iSize = xlPaperA3
Case "A4"
iSize = xlPaperA4
Case "A5"
iSize = xlPaperA5
End Select
If iSize > 0 Then _
ActiveSheet.PageSetup.PaperSize = iSize
End Sub
'Callback for DropDown1 getSelectedItemIndex
Sub DropDown1_GetSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
returnedVal = GetPageSize
End Sub
Function GetPageSize() As String
Select Case ActiveSheet.PageSetup.PaperSize
Case xlPaperA3
GetPageSize = 0 '"A3"
Case xlPaperA4
GetPageSize = 1 ' "A4"
Case xlPaperA5
GetPageSize = 2 ' "A5"
End Select
End Function
' -- ThisWorkbook
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
RibUI.InvalidateControl "DropDown1"
End Sub