Excel Ribbon dropDown: set page scale value (for mac)

Question

The first code allows you to select between three page sizes.

Trying to adapt it to set the page scale value.

Issue: DropDown2_onAction does not execute.

Page Size:

' -- 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"
                    sizeString="xxxx" 
                    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>


' -- Standard 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

Page Scale:

' -- 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="GroupDemo3" 
                label="Page Scale"
                imageMso="AddInManager">
                    <dropDown id="DropDown2"
                    sizeString="xxxx"                    
                    onAction="DropDown2_onAction"
                    getSelectedItemIndex="DropDown2_GetSelectedItemIndex"
                    >
                        <item id="Scale_100"
                        label="100%"/>
                        <item id="Scale_77"
                        label="77%"/>
                        <item id="Scale_68"
                        label="68%"/>
                    </dropDown>
                </group>                 
            </tab>
        </tabs>
    </ribbon>
</customUI>


' -- Standard Module

Option Explicit
Public RibUI As IRibbonUI

Sub LoadRibbon(Ribbon As IRibbonUI)
    Set RibUI = Ribbon
    RibUI.InvalidateControl "DropDown2"
End Sub

'Callback for DropDown2 onAction
Sub DropDown2_onAction(control As IRibbonControl, id As String, index As Integer)
    Dim iSize As Long
    Select Case Right(id, 2) ' id
        Case "100%"
             iSize = 100
        Case "77%"
            iSize = 77
        Case "68%"
            iSize = 68
    End Select
    If iSize > 0 Then _
        ActiveSheet.PageSetup.Zoom = iSize
End Sub

'Callback for DropDown2 getSelectedItemIndex
Sub DropDown2_GetSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = GetPageScale
End Sub

Function GetPageScale() As String
        Select Case ActiveSheet.PageSetup.Zoom
        Case 100
            GetPageScale = 0 ' "100%"
        Case 77
            GetPageScale = 1 ' "77%"
        Case 68
            GetPageScale = 2 ' "68%"
        End Select
End Function


' -- ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    RibUI.InvalidateControl "DropDown2"
End Sub

Answer 1

• The id property of control is different with before.
• Extract the zoom ratio with Mid and InStr

Sub DropDown2_onAction(control As IRibbonControl, id As String, index As Integer)
    Dim iLoc As Long, sZoom As String
    iLoc = InStr(id, "_")
    If iLoc = 0 Then Exit Sub
    sZoom = Mid(id, iLoc + 1)
    If IsNumeric(sZoom) Then ActiveSheet.PageSetup.Zoom = CInt(sZoom)
End Sub