CODEWITHSUNDEEP
vbaexcelribbon
Jiminy Cricket
Jiminy Cricket

Reputation: 1377

Controlling VBA Ribbon Dynamically

I have an app in which I'm using the Ribbon for the first time. I can populate my own controls and make call backs to subroutines no problem. I'm now at the stage of development where I wish to hide / show some of the Groups dynamically to provide a better user experience.

I am able to hide / show the group during the Workbook Load by changing the visible property in CallbackGetVisible but when I change the value and call RefreshRibbon whilst the application is running, it breaks at Rib.Invalidate. Testing shows me that the Rib (IRibbonUI) object (Rib) is set to nothing. Is there a way to keep the object active or is there something else I'm missing?

XML

<!-- This is example : Custom tab for your favorite macros part 1   -->
<customUI onLoad="onLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <!-- Add Custom tab to the ribbon with your favorite buttons-->
  <!-- The example add three groups to the new tab -->
  <!-- On the last tab there is a menu with five options-->
  <ribbon>
    <tabs>
      <tab id="MyCustomTab" label="MIS DASHBOARD" insertAfterMso="TabHome">
        <group id="customGroup1" label="Menus">
          <menu id="MyDropdownMenu1" label="Dashboard" size="large" imageMso="ChartTypeOtherInsertGallery">
            <button id="customButton1" label="Dashboard Filters" onAction="ShowfrmDashboardFilters" imageMso="ViewsLayoutView" />
          </menu>
          <menu id="MyDropdownMenu2" label="Reports" size="large" imageMso="SlideMasterChartPlaceholderInsert">
        <button id="customButton2" label="Hub Templates" onAction="ShowfrmReportsTemplates" imageMso="CreateTableTemplatesGallery" />
        <button id="customButton3" label="Enter Exceptions" onAction="ShowfrmReportsExceptions" imageMso="TableDesign" />
          </menu>
          <menu id="MyDropdownMenu3" label="Admin" size="large" imageMso="FileDocumentEncrypt">
            <button id="customButton8" label="Data Entry" onAction="ShowfrmAdminDataEntry" imageMso="TableDesign" />
            <button id="customButton10" label="Manage Business Priorities" onAction="ShowfrmAdminBP" imageMso="QueryShowTable" />
            <button id="customButton11" label="Manage Templates" onAction="ShowfrmAdminTemplates" imageMso="CreateTableTemplatesGallery" />
            <button id="customButton12" label="Manage Metric Templates" onAction="ShowfrmAdminMetrics" imageMso="AccessListAssets" />
          </menu>
      <menu id="StaffDatabaseMenu" label="Staff Database" size="large" imageMso="CreateTableTemplatesGallery">
            <button id="customButton50" label="Update Data" onAction="ShowfrmStaffDatabase" imageMso="CreateTableTemplatesGallery" />
        <button id="customButton51" label="Add" onAction="ShowfrmStaffDatabaseAdd" imageMso="MailMergeRecipientsEditList" />
        <button id="customButton52" label="Delete" onAction="ShowfrmStaffDatabaseDelete" imageMso="PageMenu" />
          </menu>
    <menu id="MyDropdownMenu10" label="Local Data" size="large" imageMso="CreateReportFromWizard">
            <button id="customButton60" label="EWB" onAction="ShowfrmEWB" imageMso="CreateReportFromWizard" />
          </menu>
        </group>
    <group id="customGroup4" label="Support">
        <button id="customButton20" label="Feedback" size="large" onAction="ShowFeedback" imageMso="FileSendMenu" />
        <button id="customButton21" label="Guidance" size="large" onAction="ShowGuidance" imageMso="TentativeAcceptInvitation" />
        <button id="customButton22" label="Change Control" size="large" onAction="ShowChangeControl" imageMso="ReviewDisplayForReview" />
    </group>
    <group id="customGroup5" label="Actions" getVisible="CallbackGetVisible"> 
        <button id="customButton30" label="Save" size="large" onAction="ShowGuidance" imageMso="ExportToVCardFile" />
        <button id="customButton31" label="Cancel" size="large" onAction="ShowGuidance" imageMso="OmsDelete" />
    </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Code

Option Private Module
Option Explicit
Const errModule As String = "modRibbon"

'Module Variables
Dim Rib As IRibbonUI
Public MyTag As String

Sub CallbackGetVisible(control As IRibbonControl, ByRef visible)

    visible = True

End Sub

Sub RefreshRibbon()

    Debug.Print "RefreshRibbon"
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub

'==================================================================================================================================================================
'Called On Load From XML
'==================================================================================================================================================================
Sub onLoad(ByVal ribbon As IRibbonUI)
On Error GoTo err_Handle
Const strError As String = "Error - Please Contact " & gblDeveloper & " Quoting 'OnLoad'"

    Set Rib = ribbon
    Rib.ActivateTab ("MyCustomTab")
    GoTo Cleanup

'Set Any Objects to Nothing, Exits Sub
Cleanup:
    Set Rib = Nothing
    Exit Sub
'Throw Error
err_Handle:
    errMsg strError & Chr(10) & Err.Description & Chr(10) & errModule
    Resume Cleanup
End Sub

Edit

I'm under the impression that Rib.Invalidate will cause CallbackGetVisible to run and, therefore, set the Group's visible property accordingly. But Invalidate won't run while the Ribbon is Nothing.

Upvotes: 3

Views: 5322

Answers (1)

Olle Sj&#246;gren
Olle Sj&#246;gren

Reputation: 5385

You set Rib to Nothing in the Cleanup: part of the sub onLoad.

Remove that line and you can use the Rib object as long as the code module holding the Rib variable is open.

Upvotes: 3

Related Questions