Enable/Disable Ribbon Controls Independantly

[alketrazz]

Hi


I've been searching and searching for a solution to this and although I have found lots of examples I can achieve what I'm after.


I'm very new to the creating custom ribbons so please bare with me. This is probably too lenghty but as I understand more I'm sure I'll get to the point quicker.


Firstly, this site is where I've got most of my examples from and this was my starting point. Thanks to Ron de Bruin for this as it is excellent. Change the Ribbon in Excel 2007 - 2013


What I am trying to do is essentially 2 things


1. When a ribbon control is clicked it will run some code and the disable the button.
2. When another sub routine is run I want to be able to enable that button again. (The button should be disabled by default and it would enable on this sub)


I've kind of got it working but I'm sure it's the wrong way... I'm using a public variable and then toggling the true/false state.


In my XML i'm using the call back but I don't get why it is returning all the controls that use this call back to its original state. In order to get round this I have then created different call back events.


Do you have to have separate call backs for each button? and also separate global variables to hold the state of each control. Instead of global variables to hold the state could I determine the current state of the control and the enable/disable as required.


Basically I want to enable/disable the controls independently from either the control on the ribbon or another sub.


Also, should I be using the InvalidateControl as I've read various posts regarding it being unreliable. (Although it seen to be working)


It seems like this should done in a class but I've not idea how to get that working... I've seen this in access and it looks like what I'm after Ribbon Class for Office 2007.


Here is the VBA:

Option Explicit
Public Rib As IRibbonUI
Public MyTag As String
Public bState As Boolean, bState2 As Boolean
Public RibbonTextBox As String




#If VBA7 Then
  Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
#Else
  Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
#End If




Public Sub Ribbon******(ribbon As IRibbonUI)
  ' Store pointer to IRibbonUI
  Set Rib = ribbon
  Sheet1.Range("A1").Value = ObjPtr(ribbon)
  Call RefreshRibbon(Tag:="wd")
End Sub




#If VBA7 Then
  Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
  Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If
  Dim objRibbon As Object
  CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
  Set GetRibbon = objRibbon
  Set objRibbon = Nothing
End Function




' callback for getLabel
Sub GetLabelText1(control As IRibbonControl, ByRef returnedVal)
  returnedVal = ""
End Sub




Sub GetVisible(control As IRibbonControl, ByRef visible)
  If MyTag = "show" Then
    visible = True
  Else
    If control.Tag Like MyTag Then
      visible = True
    Else
      visible = False
    End If
  End If
End Sub




Public Sub getEnabledCallback(control As IRibbonControl, ByRef enabled As Variant)
' Callback for getEnabled-Attribute of UI-controls




  ' We set the states according to Toggle12 and IAmWorking
  Select Case control.ID
    Case "btn1"
      enabled = bState
    Case "btn2"
      enabled = Not bState
    Case Else
      enabled = True
    End Select
    
End Sub




'Callback for quickSearchBox getText
Sub GetEditBoxText(control As IRibbonControl, ByRef returnedVal)
  returnedVal = ""
End Sub




Sub RefreshRibbon(Tag As String)
  MyTag = Tag
  If Rib Is Nothing Then
    Set Rib = GetRibbon(Sheets(1).Range("A1").Value)
    Rib.Invalidate
    MsgBox "The Ribbon handle was lost, Hopefully this is sorted now by the GetRibbon Function?. You can remove this msgbox, I only use it for testing"
  Else
    Rib.Invalidate
  End If
End Sub




Public Sub DoButton(ByVal control As IRibbonControl)
' The onAction callback for btn1 and btn2
  bState = Not bState ' Toggle state
  ' Reload the ribbon UI
  Call RefreshRibbon(Tag:="wd")
End Sub




'Note: Do not change the code above




'''''''''''''''''''''''''''''''''''''
''''' This code is for the tabs '''''
'''''''''''''''''''''''''''''''''''''
Sub ShowAllTabs()
'Show every Tab, Group or Control(we use the wildgard "*")
  Call RefreshRibbon(Tag:="*")
End Sub




Sub ShowOnlyMine()
  Call RefreshRibbon(Tag:="wd")
End Sub




'''''''''''''''''''''''''''''''''''''''''
''''' This code is for the edit box '''''
'''''''''''''''''''''''''''''''''''''''''




' Callback for quickSearchBox onChange
Sub SetTextValue(control As IRibbonControl, strText As String)
    RibbonTextBox = strText
    Call QuickSearchRibbon(control)
    If Rib Is Nothing Then Call RefreshRibbon(Tag:="wd")
    Rib.InvalidateControl control.ID
    RibbonTextBox = ""
End Sub




' callback for FilterNew onAction
Sub QuickSearchRibbon(control As IRibbonControl)
  If RibbonTextBox = "" Then
    MsgBox "Nothing Entered!"
  Else
    MsgBox RibbonTextBox
  End If
End Sub




'''''''''''''''''''''''''''''''''
''''' Ribbon button actions '''''
'''''''''''''''''''''''''''''''''




Sub EnableDisableButton(control As IRibbonControl, ByRef returnedVal)
  returnedVal = Not bState2
End Sub




Sub button3_Click(control As IRibbonControl)
  bState2 = Not bState2
  Rib.InvalidateControl control.ID
End Sub




Sub UpdateAndImport(control As IRibbonControl)
  bState2 = Not bState2
  Rib.InvalidateControl control.ID
End Sub




Sub OpenForm2(control As IRibbonControl)
  MsgBox "Open Form"
End Sub




''''''''''''''''''''''''''''''''''''
''''' Worksheet button actions '''''
''''''''''''''''''''''''''''''''''''




Sub EnableDisableControl_1()
  bState2 = Not bState2
  Rib.InvalidateControl "refresh"
End Sub




Sub EnableDisableControl_2()
  bState2 = Not bState2
  Rib.InvalidateControl "btn3"
End Sub




Sub EnableDisableControl_3()
  bState = Not bState
  Rib.InvalidateControl "btn1"
  Rib.InvalidateControl "btn2"
End Sub

And here is the XML (Apologies for formatting. How should XML be posted on here?

<!--?xml version="1.0" encoding="UTF-8" standalone="yes"?--><customui ******="Ribbon******" xmlns="http://schemas.microsoft.com/office/2009/07/customui">


<!-- *******************************************************************-->
<!-- *****Set startFromScratch to true to hide the Ribbon and QAT*******-->
<!-- *******************************************************************-->


<!-- Set startFromScratch to true to hide the Ribbon and QAT-->
<ribbon startfromscratch="true">


<!--<qat>
<documentControls>
<control idMso="FileNewDefault" />
<control idMso="FileOpen" />
<control idMso="FileSave" />
<control idMso="FileSendAsAttachment" />
<control idMso="FilePrintQuick" />
<control idMso="PrintPreviewAndPrint" />
<control idMso="Spelling" />
<control idMso="Undo" />
<control idMso="Redo" />
<control idMso="SortAscendingExcel" />
<control idMso="SortDescendingExcel" />
<control idMso="FileOpenRecentFile" />
</documentControls>
</qat>-->


<!-- Create a custom tab for every original tab on the Ribbon with the same name. -->
<!-- Add every origenal group to this custom tabs to duplicate the original tabs. -->
<!-- We can now use getVisible to hide/display the tabs with a VBA macro. -->
<!-- Note: Normal getVisible is not working for specific built-in tabs/groups/controls. -->


<tabs>
<tab id="MyCustomHomeTab" label="Home" insertaftermso="TabHome" getvisible="GetVisible" tag="ribhome">
<group idmso="GroupClipboard">
<group idmso="GroupFont">
<group idmso="GroupAlignmentExcel">
<group idmso="GroupNumber">
<group idmso="GroupStyles">
<group idmso="GroupCells">
<group idmso="GroupEditingExcel">
</group></group></group></group></group></group></group></tab>


<tab id="MyCustomInsertTab" label="Insert" insertaftermso="TabInsert" getvisible="GetVisible" tag="ribinsert">
<group idmso="GroupInsertTablesExcel">
<group idmso="GroupInsertIllustrations">
<group idmso="GroupInsertChartsExcel">
<group idmso="GroupInsertLinks">
<group idmso="GroupInsertText">
</group></group></group></group></group></tab>


<tab id="MyCustomPageLayoutTab" label="Page Layout" insertaftermso="TabPageLayoutExcel" getvisible="GetVisible" tag="ribpagelayout">
<group idmso="GroupThemesExcel">
<group idmso="GroupPageSetup">
<group idmso="GroupPageLayoutScaleToFit">
<group idmso="GroupPageLayoutSheetOptions">
<group idmso="GroupArrange">
</group></group></group></group></group></tab>


<tab id="MyCustomFormulasTab" label="Formulas" insertaftermso="TabFormulas" getvisible="GetVisible" tag="ribformulas">
<group idmso="GroupFunctionLibrary">
<group idmso="GroupNamedCells">
<group idmso="GroupFormulaAuditing">
<group idmso="GroupCalculation">
</group></group></group></group></tab>


<tab id="MyCustomDataTab" label="Data" insertaftermso="TabData" getvisible="GetVisible" tag="ribdata">
<group idmso="GroupGetExternalData">
<group idmso="GroupConnections">
<group idmso="GroupSortFilter">
<group idmso="GroupDataTools">
<group idmso="GroupOutline">
</group></group></group></group></group></tab>


<tab id="MyCustomReviewTab" label="Review" insertaftermso="TabReview" getvisible="GetVisible" tag="ribreview">
<group idmso="GroupProofing">
<group idmso="GroupComments">
<group idmso="GroupChangesExcel">
</group></group></group></tab>


<tab id="MyCustomViewTab" label="View" insertaftermso="TabView" getvisible="GetVisible" tag="ribview">
<group idmso="GroupWorkbookViews">
<group idmso="GroupViewShowHide">
<group idmso="GroupZoom">
<group idmso="GroupWindow">
<group idmso="GroupMacros">
</group></group></group></group></group></tab>


<tab id="MyCustomDeveloperTab" label="Developer" insertaftermso="TabDeveloper" getvisible="GetVisible" tag="ribdeveloper">
<group idmso="GroupCode">
<group idmso="GroupControls">
<group idmso="GroupXml">
<group idmso="GroupModify">
</group></group></group></group></tab>


<tab id="MyCustomTab1" label="My Custom Tab" insertaftermso="MyCustomDeveloperTab" getvisible="GetVisible" tag="wd">


<group id="myGroup1" label="Tool Bar">


<button id="menu" label="Menu"
keytip="R"
size="large"
getEnabled="getEnabledCallback"
onAction="OpenForm2"
imageMso="OpenStartPage"
tag="wdMenu" />


<separator id="Separator1">


</separator></button><button id="refresh" label="Refresh"
screentip="Import and Update"
supertip="Export latest data and re-import"
getEnabled="EnableDisableButton"
size="large"
onAction="UpdateAndImport"
imageMso="ImportExport"
tag="wdRefresh" />


<separator id="Separator2">


<labelcontrol id="Labelcontrol1" label="Quick Search">


<editbox id="quickSearch" label="Quick Search"
showLabel="false"
getText="GetEditBoxText"
getEnabled="getEnabledCallback"
onChange="SetTextValue" />

</editbox></labelcontrol></separator></button><button id="search" label="Search"
size="normal"
onAction="QuickSearchRibbon"
imageMso="FilterNew"
tag="wdSearch" />


</button><button id="btn1" label="Button1" onaction="DoButton" getenabled="getEnabledCallback">


</button><button id="btn2" label="Button2" onaction="DoButton" getenabled="getEnabledCallback">


</button><button id="btn3" label="Button3" onaction="button3_Click" getenabled="EnableDisableButton">

</button></group></tab></tabs></ribbon></customui>