Reputation: 109
This is a follow-up question to this one:
Excel Ribbon Button toggleButton: Shrink to fit (text)
The button works perfectly as long as you don't have any charts on your sheets.
If you have charts and the current selection happens to be a chart, the code stops working. Error: "Run-time error 91: Object or With block variable not set." For example, if a chart on Sheet1 is selected and you then click on Sheet2, the error message shows up.
How do you make the button work with charts in your workbook? How do you say: if the selection is a chart, "ignore and resume"?
Something like:
If Selection Is "ChartObject" Then Exit Sub Else
' -- 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="Group6" label="Alignment">
<toggleButton id="ToggleButton1"
label="Shrink to fit"
imageMso="AsianLayoutCharacterScaling"
size="large"
getPressed="ToggleButton1_Startup"
onAction="ToggleButton1_OnAction"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
' -- Standard Module
Option Explicit
Public RibUI As IRibbonUI
Sub LoadRibbon(Ribbon As IRibbonUI)
Set RibUI = Ribbon
RibUI.InvalidateControl "ToggleButton1"
End Sub
' ToggleButton 1 Startup
Sub ToggleButton1_Startup( _
ByRef control As IRibbonControl, _
ByRef returnedVal)
returnedVal = (Selection.ShrinkToFit = True)
End Sub
' ToggleButton 1 Click
Sub ToggleButton1_OnAction( _
ByRef control As IRibbonControl, _
ByRef pressed As Boolean)
Select Case pressed
Case True
Selection.ShrinkToFit = True
Case False
Selection.ShrinkToFit = False
End Select
End Sub
' -- ThisWorkbook
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
RibUI.InvalidateControl "ToggleButton1"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
RibUI.InvalidateControl "ToggleButton1"
End Sub
Upvotes: 0
Views: 63