VBA Ribbon Toggle Button

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
525
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi, I have been looking into using a toggle button on a custom ribbon, I have disabled all the ribbon buttons when double click event happens, however instead of the toggle button being greyed out, it actually disappears from the ribbon altogether until the ribbon is refreshed to enable the buttons again.

I have tried it in excel 2007, 2010 and 2016 but still happens on all 3. I know it has something to do with refreshing the ribbon as I have invalidated one control and it doesn't disappear, it's only when I refresh the whole ribbon.
Even in the xml I have tried adding and removing the get enabled part but it still happens either way, Can anyone help with this or is this an excel issue

Code below is not finished but works, I know the get enabled isn't in the toggle button code below but it's to show that it still happens without it

XML

XML:
<customUI onLoad="RibbonLoaded_myAddin" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="MyAddin" label="My Addin">

<group id="GroupA" label="Button Examples 1">
    <checkBox id="CB1" label="Gridlines" getPressed="CB1_getPressed" tag="CheckBox1" getEnabled="CustomEnabled" onAction="CB1_onAction"/>
</group>

<group id="GroupB" label="Button Examples 2">
    <toggleButton id="TB1" getLabel="TB1_getLabel" getPressed="TB1_getPressed" onAction="TB1_onAction" getImage="GetImage" size="large" />
</group> 

</tab>
</tabs>
</ribbon>
</customUI>


This Workbook Module

VBA Code:
Public WithEvents oApp As Application
Public oAllow As Boolean
Private Sub Workbook_Open()
    Set oApp = Nothing
    Set oApp = Application
    oAllow = True
End Sub
Private Sub oApp_NewWorkbook(ByVal wb As Workbook)
    Set oApp = Application
End Sub
Private Sub oApp_WorkbookActivate(ByVal wb As Workbook)
    If oAllow = False Then
        Application.Run "ThisWorkbook.Workbook_Open"
    End If
End Sub
Private Sub oApp_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
     If Not Target Is Nothing Then
          Call DisableAll
     End If
End Sub
Private Sub oApp_SheetActivate(ByVal Sh As Object)
    Call EnableCheckBox1
End Sub
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Target Is Nothing Then
    On Error Resume Next
        'Call DisableAll
        Select Case Selection.Address
            Case ActiveSheet.Cells.Address
                EnableCheckBox1
            Case Selection.EntireColumn.Address
                EnableCheckBox1
            Case Selection.EntireRow.Address
                EnableCheckBox1
            Case Selection.Address
                EnableCheckBox1
        End Select
    On Error GoTo 0
    End If
End Sub


Standard Module

VBA Code:
Option Explicit
Private TogglePressed As Boolean
Private PressedState As Boolean
Public MyTag As String
Public oRibbon As IRibbonUI

Sub RibbonLoaded_myAddin(ribbon As IRibbonUI) ' Ribbon Identifier for Callbacks and Refreshing Ribbon state
    Set oRibbon = ribbon
End Sub
Sub CustomEnabled(control As IRibbonControl, ByRef Enabled) ' XML getEnabled
    If MyTag = "Enable" Then
        Enabled = True
    Else
        If control.Tag Like MyTag Then
            Enabled = True
        Else
            Enabled = False
        End If
    End If
End Sub
Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If oRibbon Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        oRibbon.Invalidate
    End If
End Sub
Sub RefreshRibbonCB(Tag As String)
    MyTag = Tag
    If oRibbon Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        oRibbon.InvalidateControl ("CB1")
    End If
End Sub
Sub CB1_getPressed(control As IRibbonControl, ByRef returnedVal) ' CheckBox Button
    If Workbooks.Count < 2 Then
    Else
        If ActiveWindow.DisplayGridlines = True Then
            returnedVal = True
        Else
            returnedVal = False
        End If
    End If
End Sub
Sub CB1_onAction(control As IRibbonControl, pressed As Boolean) ' CheckBox Button
    If pressed = True Then
        If ActiveWindow.DisplayGridlines = True Then
            ActiveWindow.DisplayGridlines = True
            pressed = True
        Else
            ActiveWindow.DisplayGridlines = True
            pressed = True
        End If
    Else
        If ActiveWindow.DisplayGridlines = False Then
            ActiveWindow.DisplayGridlines = False
            pressed = False
        Else
            ActiveWindow.DisplayGridlines = False
            pressed = False
        End If
    End If
End Sub
Sub TB1_OnAction(control As IRibbonControl, pressed As Boolean) 'Toggle Button
    Select Case control.id
        Case "TB1"
            Select Case pressed
            Case True
                PressedState = True
                TogglePressed = True
            Case False
                PressedState = False
                TogglePressed = False
            End Select
        End Select
        oRibbon.InvalidateControl ("TB1")
        'oRibbon.Invalidate
End Sub
Sub GetImage(control As IRibbonControl, ByRef image) ' Toggle Button
    Select Case control.id
        Case "TB1"
            Select Case PressedState
                Case True
                    image = "ObjectNudgeLeft"
                Case False
                    image = "ObjectNudgeRight"
            End Select
    End Select
End Sub
Public Sub TB1_getLabel(control As IRibbonControl, ByRef returnVal) ' Toggle Button
    Select Case control.id
        Case "TB1"
            If TogglePressed Then
                returnVal = "Toggle Off"
            Else
                returnVal = "Toggle On"
            End If
    End Select
End Sub
Public Sub TB1_getPressed(control As IRibbonControl, ByRef returnVal) ' Toggle Button
    Select Case control.id
    Case "TB1"
        returnVal = TogglePressed
    End Select
End Sub
Sub EnableCheckBox1()
    Call RefreshRibbon(Tag:="*CheckBox1*")
    'Call RefreshRibbonCB(Tag:="*CheckBox1*")
End Sub
Sub DisableAll()
'Disable all controls
    Call RefreshRibbon(Tag:="")
End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,129,585
Messages
5,637,224
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top