Reputation: 109
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
Upvotes: 1
Views: 56
Reputation: 18778
id
property of control is different with before.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
Upvotes: 1