user23636411
user23636411

Reputation: 109

Excel Ribbon Button toggleButton: Shrink to fit (text) - exclude charts from selection

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

Answers (0)

Related Questions