eBMC – Consolidation model

Declaration of a ribbon for a report for the consolidation model of eBMC.

eBMC_Conso

The button Show empty rows is a toggle button: the button can be pressed; it is then active.

Toggle_state_pressed


' ---------------------------------------
'
' Hide or display rows with zero's
'
' ---------------------------------------

Sub ClickShowEmptyRows(control As IRibbonControl, pressed As Boolean)

    If Variables.cEPM Is Nothing Then Set Variables.cEPM = New clsCAVOEPM

    ' Hide or Show depending on the state of the toggle button
    ' pressed = true  → Show empty rows
    ' pressed = false → Hide empty rows

    Call cEPM.SetSheetOptions(shBalance, HideRowAndZeros, Not pressed)
    Call cEPM.Refresh(bActiveSheetOnly:=True)

End Sub

1. Manifest

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Toolbar.Ribbon_onLoad">
   <ribbon startFromScratch="false">
      <tabs>
         <tab idMso="TabFile" visible="true" /> 
         <tab id="eBMC" label="eBMC" insertBeforeMso="TabHome">            
            <group id="grpeBMC_EPM" label="Actions">
               <button id="btnEPMRefresh" label="Refresh" size="large" onAction="Toolbar.clickEPMRefresh" imageMso="DataRefreshAll" tag="EPM"
                  supertip="Get the newer version of the information from the database.  Be carefull : if you've made changes without saving them, by refreshing the sheet, yours changes will be lost.&#xD;&#xD;Shortcut : CTRL-r"/>
               <button id="btnEPMSubmit" label="Save data" size="large" onAction="Toolbar.clickEPMSubmit" imageMso="DatabaseSqlServer" tag="EPM" 
                  supertip="Submitting your changes will store them in the central database; figures will then be available to others&#xD;&#xD;Shortcut : CTRL-s"/>
               <separator id="grpeBMC_EPM_Sep02"/>
               <button id="btnEPMClose" label="Close" size="large" onAction="Toolbar.clickEPMClose" imageMso="FileClose" tag="EPM"
                  supertip="Get the newer version of the information from the database.  Be carefull : if you've made changes without saving them, by refreshing the sheet, yours changes will be lost.&#xD;&#xD;Shortcut : CTRL-r"/>
            </group>
            <group id="grpeBMC_GUID" label="User interface">
               <toggleButton id="btnShowEmptyRows" size="large" label="Show empty rows" imageMso="DatasheetView" 
                  onAction="Toolbar.clickShowEmptyRows"
                  supertip="Toggle the visibility of empty rows in the report" />
            </group>
            <group id="grpeBMC_About" label="About">               
               <button id="btneBMCAbout" label="About" onAction="clickShowAbout" imageMso="BlogHomePage" />
            </group>
         </tab>
      </tabs>
   </ribbon>
</customUI>

2. VBA

Associated VBA code (in a module called Toolbar, thats why callbacks starts with Toolbar)

Attribute VB_Name = "Toolbar"
' --------------------------------------------------------------------------------------------
'
' Author       : AVONTURE Christophe
'
' Aim          : Toolbar's event handler
'
' Written date : June 2018
'
' --------------------------------------------------------------------------------------------

Option Explicit
Option Compare Text
Option Base 1

' --------------------------------------------------------------------------------------------
'
' Initialization code for the Ribbon. Called once the ribbon is being loaded
'
' --------------------------------------------------------------------------------------------

Public Sub Ribbon_onLoad(ribbon As IRibbonUI)

    If (Constants.cDebug) Then
        Debug.Print "Toolbar::Ribbon_onLoad - Start"
    End If

    Application.EnableCancelKey = XlEnableCancelKey.xlDisabled

    Application.ScreenUpdating = False

End Sub

' --------------------------------------------------------------------------------------------
'
' Shortcut to the Refresh button of the EPM toolbar. Refresh the active sheet
'
' --------------------------------------------------------------------------------------------

Sub clickEPMRefresh(control As IRibbonControl)

    If (Variables.cEPM Is Nothing) Then Set Variables.cEPM = New clsCAVOEPM
    Call cEPM.Refresh(bActiveSheetOnly:=True)

End Sub

' --------------------------------------------------------------------------------------------
'
' Shortcut to the Submit button of the EPM toolbar.
'
' --------------------------------------------------------------------------------------------

Sub clickEPMSubmit(control As IRibbonControl)

    If (Variables.cEPM Is Nothing) Then Set Variables.cEPM = New clsCAVOEPM

    Call cEPM.Submit

End Sub

' --------------------------------------------------------------------------------------------
'
' The toggle ProtectSheet depends on the actual state of ProtectContents
'
' --------------------------------------------------------------------------------------------

Sub pressedShowEmptyRows(control As IRibbonControl, ByRef pressed)

Dim bValue As Boolean

    If (Variables.cEPM Is Nothing) Then Set Variables.cEPM = New clsCAVOEPM

    bValue = cEPM.GetSheetOptions(shBalance, HideRowAndZeros)

    pressed = Not bValue

End Sub

' --------------------------------------------------------------------------------------------
'
' Hide or display rows with zero's
'
' --------------------------------------------------------------------------------------------

Sub ClickShowEmptyRows(control As IRibbonControl, pressed As Boolean)

    If Variables.cEPM Is Nothing Then Set Variables.cEPM = New clsCAVOEPM

    ' Hide or Show depending on the state of the toggle button
    ' pressed = true  â&#135;&#146; Show empty rows
    ' pressed = false â&#135;&#146; Hide empty rows

    Call cEPM.SetSheetOptions(shBalance, HideRowAndZeros, Not pressed)
    Call cEPM.Refresh(bActiveSheetOnly:=True)

End Sub

' --------------------------------------------------------------------------------------------
'
' Close button => quit the application
'
' --------------------------------------------------------------------------------------------

Sub clickEPMClose(control As IRibbonControl)

    Application.Quit

End Sub

' --------------------------------------------------------------------------------------------
'
' Display the About sheet
'
' --------------------------------------------------------------------------------------------

Sub clickShowAbout(control As IRibbonControl)

    Application.EnableCancelKey = XlEnableCancelKey.xlDisabled
    MsgBox "About message", vbInformation + vbOKOnly, "eBMC - Conso"

End Sub