decadence
Well-known Member
- Joined
- Oct 9, 2015
- Messages
- 525
- Office Version
- 365
- 2016
- 2013
- 2010
- 2007
- Platform
- 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
This Workbook Module
Standard Module
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