CAVO_AddIn

Declaration of the ribbon for the CAVO_AddIn Excel addin which provides generic feature for working easier with Excel like, f.i., creating an email and put the selected range (in Excel) in the body part of the mail (in a HTML format).

This sample show how to implement menu items: show a button and, by clicking on that button, display a menu with a list of choices.

A lot of features are disabled here below because they need that at least one workbook is opened and, when the screen capture has been made, this was not the case.

CAVO_AddIn

This is made possible thanks the getEnabled attribute like illustrated below.

<menu id="CAVOXLAddin_Workbook" ... getEnabled="CAVOXLAddin_enableWorkbook" >
  YOUR FEATURES
</menu>

CAVOXLAddin_enableWorkbook is the name of a subroutine that will return False when no workbook is opened:

Sub CAVOXLAddin_enableWorkbook(control As IRibbonControl, ByRef Enabled)
    Enabled = (Application.Workbooks.Count > 0)
End Sub

1. Manifest

<customUI onLoad="CAVO_RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="CAVOXLAddin" label="Budget" insertBeforeMso="TabHome" keytip="CA" getVisible="getVisibleCAVO_Ribbon"> 

            <group id="CAVOXLAddin_Main" label="General" autoScale="true"> 
               <button id="CAVOXLAddin_MainSave" label="Save All" size="large" imageMso="FileSave" onAction="clickCAVO_MainBtnSaveAll" supertip="Save all opened workbooks and loaded add-ins (only if modified). Events will first be disabled to prevent conflicts while saving."/>
               <separator id="CAVOXLAddin_Main_Sep01"/>
               <toggleButton id="CAVOXLAddin_VBADebugMode" size="normal" label="Debug" image="bugs" supertip="Enabled / Disable the debug mode. If enabled, you'll get more debugging informations in the Debug VBE pane and the screen can display more info too like showing sheets, progression, ... that are hidden otherwise" onAction="clickCAVO_VBADebugMode" getPressed="pressedCAVO_VBADebugMode" />
               <toggleButton id="CAVOXLAddin_VBAFullDebugMode" size="normal" label="Full Debug" image="bugs" supertip="Enabled / Disable the *full* debug mode. If enabled, you'll get more debugging informations in the Debug VBE pane and the screen can display more info too like showing sheets, progression, ... that are hidden otherwise" onAction="clickCAVO_VBAFullDebugMode" getPressed="pressedCAVO_VBAFullDebugMode" />
            </group>

            <group id="CAVOXLAddin_Workbook" label="Workbook" autoScale="true"> 

                  <button id="CAVOXLAddin_WorkbookBtnMail" label="Send by mail" imageMso="SharingOpenMailFolder" onAction="clickCAVO_WorkbookBtnMail" supertip="Send the current workbook by email" getEnabled="CAVOXLAddin_enableWorkbook"/>

               <toggleButton id="CAVOXLAddin_WorkbookBtnProtect" label="Protect" size="normal" getEnabled="CAVOXLAddin_enableWorkbook" onAction="clickCAVO_WorkbookBtnProtect" getPressed="pressedCAVO_WorkbookBtnProtect" imageMso="Lock" supertip="Protect / unprotect the active workbook against changes in the 
structure "/>
               <menu id="CAVOXLAddin_WorkbookDebug" label="Debug" itemSize="normal" imageMso="Info" supertip="Debugging functionnalities" getEnabled="CAVOXLAddin_enableWorkbook" > 
                  <button id="CAVOXLAddin_WorkbookBtnGetNames" imageMso="Info" label="Debug - Get list of properties" onAction="clickCAVO_WorkbookBtnGetNames" supertip="Create a new workbook and list there all properties (custom included) found in this workbook" /> 
               </menu>
               <button id="CAVOXLAddin_WorkbookBtnDuplicate" label="Duplicate" imageMso="ReplicationRecoverDesignMaster" onAction="clickCAVO_WorkbookBtnDuplicate" supertip="The result is like a SaveAs but here a new workbook is created and every sheets, modules, classes, forms, names, custom properties, ... are copied into the new workbook. This is usefull when the active workbook is corrupt / when Excel doesn't want to save the file." getEnabled="CAVOXLAddin_enableWorkbook"/>
               <button id="CAVOXLAddin_WorkbookBtnCompress" label="Compress" imageMso="AdpDiagramRecalculatePageBreaks" onAction="clickCAVO_WorkbookBtnCompress" getEnabled="CAVOXLAddin_enableWorkbook" supertip="Sometimes, when pressing CTRL-END to go to the last used cell, Excel goes too far and consider a lot of empty rows / columns as 'used'. This is a negative impact on the size of the file and can have a negative impact too on somes processing. Compressing the sheet will removes theses unused rowd / columns." />
            </group>

            <group id="CAVOXLAddin_Links" label="Links" autoScale="false"> 
               <button id="CAVOXLAddin_LinksList" label="Get links" size="normal" imageMso="FileLinksToFiles" onAction="clickCAVO_LinksBtnGetWBLinks" supertip="Generate a new file that will contains every links found in the current workbook" getEnabled="CAVOXLAddin_enableLinks"/>
            </group>

            <group id="CAVOXLAddin_Sheets" label="Sheets" autoScale="true"> 
               <menu id="CAVOXLAddin_SheetsExport" label="Export/Mail" itemSize="large" imageMso="ExportExcel" supertip="Export or send by mail" getEnabled="CAVOXLAddin_enableWorkbook" >
                  <button id="CAVOXLAddin_SheetBtnMail" label="Send by mail" onAction="clickCAVO_SheetBtnMail" supertip="Copy the current sheet to a new workbook that will then be sent by email" imageMso="SharingOpenMailFolder"/>
                  <button id="CAVOXLAddin_SheetBtnExport" label="Export" imageMso="ExportExcel" onAction="clickCAVO_SheetBtnExport" supertip="Export current sheet into a new workbook; copy/paste values" />
                  <button id="CAVOXLAddin_SheetBtnMailSelection" label="Send selection by mail" imageMso="MailMergeStartMailMergeMenu" onAction="clickCAVO_SheetBtnMailSelection" supertip="Send the selection (if any) or send the sheet used range by email, the range will be converted as HTML and then appended to the mail body" />
               </menu>
               <menu id="CAVOXLAddin_SheetsVisibility" label="Visibility" itemSize="large" imageMso="DatasheetView" supertip="Adjust the visiblity of worksheets" getEnabled="CAVOXLAddin_enableSheets"> 
                  <button id="CAVOXLAddin_SheetBtnShowOne" label="Show a specific sheet" imageMso="BrightnessMore" onAction="clickCAVO_SheetBtnShowOne" supertip="If you know the name of the hidden sheet, clic here, give the name and the sheet will then be displayed" />
                  <button id="CAVOXLAddin_SheetBtnHideOne" label="Very hide the active sheet" imageMso="BrightnessLess" onAction="clickCAVO_SheetBtnHideOne" supertip="Very hide the active" />
                  <menuSeparator id="CAVOXLAddin_SheetsVisibilitysep1" />
                  <button id="CAVOXLAddin_SheetBtnShowAll" label="Show All" imageMso="ZoomIn" onAction="clickCAVO_SheetBtnShowAll" supertip="Show all worksheets" />
                  <button id="CAVOXLAddin_SheetBtnHideAll" label="Hide All except active sheet" imageMso="ZoomOut" onAction="clickCAVO_SheetBtnHideAll" supertip="Hide all worksheets except the active one" />
               </menu>
               <menu id="CAVOXLAddin_SheetsProtection" label="Protection" itemSize="large" imageMso="Lock" supertip="Change sheet(s) protection" getEnabled="CAVOXLAddin_enableSheets">
                  <toggleButton id="CAVOXLAddin_SheetBtnProtect" label="Protect" getEnabled="CAVOXLAddin_enableSheets" onAction="clickCAVO_SheetBtnProtect" getPressed="pressedCAVO_SheetBtnProtect" imageMso="Lock" supertip="Protect / unprotect the active sheet gainst changes"/>
                  <button id="CAVOXLAddin_SheetBtnUnProtectAll" label="Unprotect all" imageMso="Lock" onAction="clickCAVO_SheetBtnUnProtectAll" supertip="Unprotect every single worksheets in the loaded workbook" />
                  <button id="CAVOXLAddin_SheetBtnBreakProtection" label="Remove the protection" imageMso="Lock" onAction="clickCAVO_SheetBtnBreakProtection" supertip="Remove the current sheet protection by first resetting it to a nullstring." />
               </menu>

               <button id="CAVOXLAddin_SheetsBtnCompress" label="Compress" imageMso="AdpDiagramRecalculatePageBreaks" onAction="clickCAVO_SheetsBtnCompress" getEnabled="CAVOXLAddin_enableSheets" supertip="Sometimes, when pressing CTRL-END to go to the last used cell, Excel goes too far and consider a lot of empty rows / columns as 'used'. This is a negative impact on the size of the file and can have a negative impact too on somes processing. Compressing that sheet will removes theses unused rowd / columns." />

            </group>

            <group id="CAVOXLAddin_Ranges" label="Ranges" autoScale="true">
               <button id="CAVOXLAddin_DeleteEmptyRows" label="Delete empty rows" onAction="clickCAVO_RangesBtnDeleteEmptyRows" 
                  imageMso="TableDeleteRowsAndColumnsMenuWord" getEnabled="CAVOXLAddin_enableRanges"
                  supertip="Scan the current worksheet and remove all empty rows (usefull when, f.i. one line on two is a blank row)"/>
               <button id="CAVOXLAddin_DummyFigures" label="Dummy figures" onAction="clickCAVO_RangesBtnDummyFigures" 
                  imageMso="QueryBuilder" getEnabled="CAVOXLAddin_enableRanges"
                  supertip="This functionnality will generate random numbers to let you to quickly initialize cells with dummy figures "/>
               <toggleButton id="CAVOXLAddin_ShowHideHiddenRanges" getEnabled="CAVOXLAddin_enableRanges"
                  getLabel="CAVOXLAddin_getLabelShowHideHiddenRanges"
                  imageMso="PictureBrightnessGallery" onAction="Toolbar.clickCAVO_ShowHideHiddenRanges" getPressed="Toolbar.pressedShowHideHiddenRanges"
                  supertip="Toggle the visibility of the rows and columns inside named ranges having a name starting with the '_HiddenCols' or '_HiddenRows' prefix (like '_HiddenCols1' f.i.)"/>
            </group>

            <group id="CAVOXLAddin_CondFormatting" label="Formatting" autoScale="true"> 
               <button id="CAVOXLAddin_CondFormattingHighLight" label="Highlight" imageMso="FileLinksToFiles" onAction="clickCAVO_CondFormattingBtnHighLight" supertip="Hide all cells withoug conditional formatting allowing to see which ones have a conditional formatting. Call the function a second time to restore cells visibility" getEnabled="CAVOXLAddin_enableCondFormatting"/>
               <button id="CAVOXLAddin_CondFormattingClear" label="Clear" imageMso="InkEraseMode" onAction="clickCAVO_CondFormattingBtnClear" supertip="Remove conditional formatting." getEnabled="CAVOXLAddin_enableCondFormatting"/>
               <button id="CAVOXLAddin_CondFormattingOK_ERROR" label="OK / Error" imageMso="PersonaStatusOnline" onAction="clickCAVO_CondFormattingBtnOKError" supertip="Apply an OK-ERROR conditional formatting to the selection. OK cells should contains the figure greater than zero; cells with an error a figure smaller than zero. No formatting will be applied to cells with the zero value." getEnabled="CAVOXLAddin_enableCondFormatting"/>
               <button id="CAVOXLAddin_CondFormattingBtnOddEven" label="Odd / Even" imageMso="TableRowSelect" onAction="clickCAVO_CondFormattingBtnOddEven" supertip="Apply an odd-even conditional formatting to the selection : put one line on two with a gray background color." getEnabled="CAVOXLAddin_enableCondFormatting"/>
               <button id="CAVOXLAddin_CondFormattingBtnDupValues" label="Duplicate" imageMso="PersonaStatusBusy" onAction="clickCAVO_CondFormattingBtnDupValues" supertip="Put in red cells with a duplicate value i.e. with the same value that on another cell in the selected range" getEnabled="CAVOXLAddin_enableCondFormatting"/>
            </group>

            <group id="CAVOXLAddin_Formulas" label="Formulas" autoScale="true"> 
               <button id="CAVOXLAddin_FormulasHighlight" label="Highlight" imageMso="StopLtrHover" onAction="clickCAVO_FormulasBtnHighlight" getEnabled="CAVOXLAddin_enableFormulas" supertip="Put cells with a formulas in evidence by setting the cell's background color in yellow. Click once again on this button to restore the original cells's background color" />
               <menu id="CAVOXLAddin_FormulasDebug" label="Debug" itemSize="large" imageMso="Info" supertip="Debugging functionnalities" getEnabled="CAVOXLAddin_enableFormulas" > 
                  <button id="CAVOXLAddin_FormulasBtnCount" imageMso="Info" label="Debug - Count the number of formulas in the workbook" onAction="clickCAVO_FormulasBtnCount" supertip="Count the number of formulas in the workbook" /> 
                  <button id="CAVOXLAddin_FormulasBtnFindCircularRef" imageMso="CircularReferences" label="Debug - Find circular references" onAction="clickCAVO_FormulasBtnFindCircularRef" supertip="Check if the workbook contains circular references and if so, output the list in a new workbook" /> 
               </menu>
            </group>

            <group id="CAVOXLAddin_Names" label="Names" autoScale="true"> 
               <button id="CAVOXLAddin_NamesRemoveAll" label="Remove all" imageMso="ReviewDeleteAllMarkupOnSlide" onAction="clickCAVO_NamesBtnRemoveAll" supertip="Remove every names from the workbook : delete every global names and every local ones. Be sure that it's what you want to do" getEnabled="CAVOXLAddin_enableNames" />
               <menu id="CAVOXLAddin_NamesScope" label="Scope" itemSize="large" imageMso="RecurrenceEdit" supertip="Change scope" getEnabled="CAVOXLAddin_enableNames" > 
                  <button id="CAVOXLAddin_NamesBtnToggleGlobalToLocal" label="Change global names to local ones" imageMso="FontsReplaceFonts" onAction="clickCAVO_NamesBtnToggleGlobalToLocal" supertip="Retrieve all names defined in the active sheet and having a global scope. Convert them into local names. After the execution of this function, all names defined in this sheet will be local names." />
               </menu>
               <menu id="CAVOXLAddin_NamesVisibility" label="Visibility" itemSize="large" imageMso="WordArtEditTextClassic" supertip="Adjust the visiblity of worksheets and workbook'names" getEnabled="CAVOXLAddin_enableNames" > 
                  <button id="CAVOXLAddin_NamesBtnShowAll" label="Show All" imageMso="ZoomIn" onAction="clickCAVO_NamesBtnShowAll" supertip="Show all names" />
                  <button id="CAVOXLAddin_NamesBtnHideAll" label="Hide All" imageMso="ZoomOut" onAction="clickCAVO_NamesBtnHideAll" supertip="Hide all names" />
               </menu>
               <menu id="CAVOXLAddin_NamesDebug" label="Debug" itemSize="large" imageMso="Info" supertip="Debugging functionnalities" getEnabled="CAVOXLAddin_enableNames" > 
                  <button id="CAVOXLAddin_NamesBtnGetNames" imageMso="Info" label="Debug - Get list of names" onAction="clickCAVO_NamesBtnGetNames" supertip="Create a new workbook and list there all names found in this workbook" /> 
               </menu>
            </group>

            <group id="CAVOXLAddin_Data" label="Data" autoScale="true">
               <menu id="CAVOXLAddin_DataBtn" label="Connections" itemSize="large" imageMso="CreateDiagram" supertip="Data connection functionnality" getEnabled="CAVOXLAddin_enableData" >
                  <button id="CAVOXLAddin_DataBtnAddQueryTable" label="Add a query" imageMso="ViewsAdpDiagramSqlView" onAction="clickCAVO_DataBtnAddQueryTable" supertip="Add a query table in the active workbook" />  
               </menu>
               <button id="CAVOXLAddin_DataBtnRunSQL" label="Run a SQL" imageMso="ViewsAdpDiagramSqlView" getEnabled="CAVOXLAddin_enableBtnRunSQL" onAction="clickCAVO_DataBtnRunSQL" supertip="Copy/Paste here a SELECT SQL statement and the recordset will be exported in a new worksheet" />  
               <menu id="CAVOXLAddin_DataDebug" label="Debug" itemSize="large" imageMso="Info" supertip="Debugging functionnalities" getEnabled="CAVOXLAddin_enableData" >
                  <button id="CAVOXLAddin_DataBtnGetWBConnections" label="Debug - Get list of data connections" imageMso="Info" onAction="clickCAVO_DataBtnGetWBConnections" supertip="Retrieve all data connection in the active workbook and output this info in a new worksheet" />               
               </menu>
            </group>

            <group id="CAVOXLAddin_VBA" label="VBA" autoScale="true">
               <menu id="CAVOXLAddin_VBABtn" label="VBA" itemSize="large" image="vba" supertip="Coding misc. functionnalities" getEnabled="CAVOXLAddin_enableVBA" >
                  <button id="CAVOXLAddin_VBABtnBackupCode" label="Backup the VBA code" onAction="clickCAVO_VBABtnBackupCode" supertip="Export all VBA code within the current workbook and generates files on the disk. Ideal to take a backup of the workbook programmation" imageMso="FileSave"/>
                  <button id="CAVOXLAddin_VBABtnReplaceRefences" label="Replace References" onAction="clickCAVO_VBABtnReplaceReference" supertip="Loop all references in the active workbook and replace the folder path of the addins to an another one." imageMso="HyperlinksVerify"/>
                  <button id="CAVOXLAddin_VBABtnToggleIsAddIn" label="Toggle isAddIn" onAction="clickCAVO_VBABtnToggleIsAddIn" supertip="Toggle the visibility of loaded addins (.xlam files)." imageMso="RecurrenceEdit"/>
               </menu>  
               <toggleButton id="CAVOXLAddin_VBAEnableEvents" size="normal" label="Enable events" imageMso="CreateModule" supertip="Enable / Disable application's events" onAction="clickCAVO_VBAEnableEvents" getPressed="pressedCAVO_VBAEnableEvents" />
               <menu id="CAVOXLAddin_VBADebug" label="Debug" itemSize="large" imageMso="Info" supertip="Debugging functionnalities" getEnabled="CAVOXLAddin_enableVBA" > 
                  <button id="CAVOXLAddin_VBABtnCountLines" imageMso="Info" label="Debug - Count the number of lines in VBA modules" onAction="clickCAVO_VBABtnCountLines" supertip="Count the number of lines in the modules, classes, ... of the workbook" />
                  <button id="CAVOXLAddin_VBABtnGetReferences" imageMso="Info" label="Debug - Get list of references" onAction="clickCAVO_VBABtnGetReferences" supertip="Retrieve the list of VB references (.xlam, .dll, ...) used by the workbook" /> 
               </menu>
            </group>

            <group id="CAVOXLAddin_Misc" label="Misc" autoScale="false">
               <button id="CAVOXLAddin_MiscBtnCrypt" size="normal" label="Crypt" onAction="clickCAVO_MiscBtnCrypt" supertip="Crypt/uncrypt a keyword" imageMso="HangulHanjaConversion"/>
            </group>

            <group id="CAVOXLAddin_AboutGroup" label="About" autoScale="false">
               <button id="CAVOXLAddin_AboutBtn" size="large" onAction="clickCAVO_ShowAbout" image="BOSA" supertip="About"/>
            </group>
         </tab>
      </tabs>
   </ribbon>
</customUI>

2. VBA

Attribute VB_Name = "Toolbar"
' ---------------------------------------------------------------------------------------------------------------------------
'
' Author : AVONTURE Christophe
'
' Aim    : CAVO_AddIn - Module Toolbar - Handler of the Ribbon
'
' Date   : August 2015
'
' Changes :
'
' Date/Comment
'
' ---------------------------------------------------------------------------------------------------------------------------

Option Explicit
Option Compare Text
Option Base 1

Private bShowHiddenHiddenRanges As Boolean
Private bShowHiddenBPCFormattingSheets As Boolean

' =
' =
'  RIBBON FUNCTIONNALITIES 
' =
' =

' ------------------------------------------------------------------------------
'
' Function called automatically by Excel when loading the toolbar (=the ribbon).
' This is done during the opening of the workbook
'
' ------------------------------------------------------------------------------

Private Sub CAVO_RibbonOnLoad(ribbon As IRibbonUI)

   Application.EnableCancelKey = XlEnableCancelKey.xlDisabled

   Set CAVO_AddIn.ribbon = ribbon

End Sub

' ------------------------------------------------------------------------------
'
' Who can see the ribbon of the CAVO AddIn ?
'
' ------------------------------------------------------------------------------

Sub getVisibleCAVO_Ribbon(control As IRibbonControl, ByRef returnedVal)

Dim bCanSee As Boolean
Dim sUserName As String

   On Error Resume Next
   ' Get the connected username
   sUserName = CAVO_AddIn.cUser.Name
   ' Check if the user can see the ribbon
   If Application.WorksheetFunction.CountA(shSettings.Range("settings_ribbon_users")) = 0 Then
      ' The list of users is empty â&#135;&#146; every body can see the ribbon
      bCanSee = True
   Else
      ' There is at least one user â&#135;&#146; only the users specified in the list can see the ribbon
      bCanSee = (Application.WorksheetFunction.VLookup(sUserName, shSettings.Range("settings_ribbon_users"), 1, False) = sUserName)
   End If
   On Error GoTo 0

   returnedVal = bCanSee
End Sub

' =
' =
'  GLOBAL FUNCTIONNALITIES 
' =
' =

Sub clickCAVO_MainBtnSaveAll(control As IRibbonControl)

   If (cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cWorkbook.SaveAllAddInAndWorkbooks

End Sub

' =
' =
'  WORKBOOKS FUNCTIONNALITIES =
' =
' =

Sub CAVOXLAddin_enableWorkbook(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the list of all properties (custom or not) in the active workbook and output this
' list in a new worksheet
'
' ------------------------------------------------------------------------------

Sub clickCAVO_WorkbookBtnGetNames(control As IRibbonControl)

   If (cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cWorkbook.Debug_ListProperties(, ActiveWorkbook, False)

End Sub

' ------------------------------------------------------------------------------
'
' Compress the active workbook
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_WorkbookBtnCompress(control As IRibbonControl)
   If (CAVO_AddIn.cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cWorkbook.Compress(ActiveWorkbook)
End Sub

' ------------------------------------------------------------------------------
'
' Send the ActiveWorkbook by email
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_WorkbookBtnMail(control As IRibbonControl)

Dim sSubject As String, sFileName As String
Dim bAlerts As Boolean

   On Error Resume Next

   If Not (ActiveWorkbook.Saved) Then

      On Error Resume Next
      'ActiveWorkbook.BuiltinDocumentProperties("Title") = wbSource.BuiltinDocumentProperties("Title")

      bAlerts = Application.DisplayAlerts
      Application.DisplayAlerts = False

      ActiveWorkbook.SaveAs cFolder.GetTempFolderName &amp; ActiveSheet.Name

      Application.DisplayAlerts = bAlerts

      If Err.Number <> 0 Then ActiveWorkbook.SaveAs cFile.GetTempFileName(sExtension:="xlsx")

      On Error GoTo 0

   End If

   ' Get the filename

   sFileName = ActiveWorkbook.FullName

   On Error Resume Next
   sSubject = ActiveWorkbook.BuiltinDocumentProperties("Title")
   If Err.Number <> 0 Then sSubject = cFile.GetFileName(sFileName)
   Err.Clear
   On Error GoTo 0

   If (cMail Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cMail.SendMail(Subject:=sSubject, AttachFileName:=sFileName)

End Sub

' ------------------------------------------------------------------------------
'
' Duplicate the workbook.   The result is like a SaveAs but here a new workbook is
' created and every sheets, modules, classes, forms, names, custom properties, ... are copied into the
' new workbook.   This is usefull when the active workbook is corrupt / when Excel doesn't want to save
' it.
'
' By duplicating the workbook, Excel will be able to save the new one.
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_WorkbookBtnDuplicate(control As IRibbonControl)

Dim wbNew As Workbook, wbSource As Workbook
Dim sFileName As String, sFileExt As String

   Set wbSource = ActiveWorkbook

   If (cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize

   Set wbNew = cWorkbook.Duplicate(wbSource)

   With wbSource

      If .Name <> vbNullString Then

         sFileExt = CAVO_AddIn.cFile.GetExtension(.Name)

         sFileName = CAVO_AddIn.cFile.RemoveExtension(.Name) &amp; "_DUPLICATE." &amp; sFileExt
         sFileName = InputBox("Please give a name for the duplicated file", cTitle, .Path &amp; "\" &amp; sFileName)

         If (sFileName <> vbNullString) Then

            If (sFileExt = "xlam") Or (sFileExt = "xla") Then sFileName = Replace(sFileName, sFileExt, "xlsm")

            wbNew.SaveAs sFileName, FileFormat:=wbSource.FileFormat

         End If ' If (sFileName <> vbNullString ) Then

      End If ' If .Name <> vbNullString  Then

   End With ' With wbSource

   Set wbSource = Nothing
   Set wbNew = Nothing

End Sub

' ------------------------------------------------------------------------------
'
' Protect the current sheet
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_WorkbookBtnProtect(control As IRibbonControl, pressed As Boolean)

Static sPassword As String

Dim sQuestion As String

   If (cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize

   If CAVO_AddIn.cPassword.Password = vbNullString Then

      If ActiveWorkbook.ProtectStructure Then
         sQuestion = "This workbook is protected.  Please enter the password to use to unprotect it"
      Else
         sQuestion = "Please enter the password to use to protect the workbook"
      End If

      If (sPassword = vbNullString) Then sPassword = InputBox(sQuestion, cTitle, "MySecretPassword")

   Else

      If (cPassword.isObfuscated) Then sPassword = cFunctions.Obfusc(cPassword.Password, False) Else sPassword = cPassword.Password

   End If

   If ActiveWorkbook.ProtectStructure Then
      Call cWorkbook.Unprotect(sPassword, ActiveWorkbook)
   Else
      Call cWorkbook.Protect(sPassword, ActiveWorkbook)
   End If

   pressed = ActiveWorkbook.ProtectStructure

End Sub

' ------------------------------------------------------------------------------
'
' Does the active workbook is protected ?
'
' ------------------------------------------------------------------------------

Sub pressedCAVO_WorkbookBtnProtect(control As IRibbonControl, ByRef returnedVal)
   If (Application.Workbooks.Count > 0) Then returnedVal = (ActiveWorkbook.ProtectStructure)
End Sub

' =
' =
'  RANGES FUNCTIONNALITIES 
' =
' =

' ------------------------------------------------------------------------------
'
' Remove all empty rows
'
' ------------------------------------------------------------------------------

Sub clickCAVO_RangesBtnDeleteEmptyRows(control As IRibbonControl)

   If (cRange Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cRange.DeleteBlankRows(ActiveSheet, ActiveSheet.UsedRange)

End Sub

' ------------------------------------------------------------------------------
'
' Add dummy figures in the selected range
'
' ------------------------------------------------------------------------------

Sub clickCAVO_RangesBtnDummyFigures(control As IRibbonControl)

Dim rng As Range
Dim wMin As Double, wMax As Double
Dim bContinue As Boolean

   If (Selection Is Nothing) Or (Selection.Cells.Count < 2) Then

      MsgBox "Please first select a range (f.i. $A$1:$D:254)", vbExclamation, CAVO_AddIn.cTitle

   Else

      bContinue = True

      On Error Resume Next
      wMin = InputBox("This functionnality will generate random numbers to let you to quickly initialize cells with dummy figures " &amp; vbCrLf &amp; _
         vbCrLf &amp; "Please specify the lowest possible value", CAVO_AddIn.cTitle, "0")
      If Err.Number <> 0 Then bContinue = False
      On Error GoTo 0

      If bContinue Then
         On Error Resume Next
         wMax = InputBox("Please specify the highest possible value", CAVO_AddIn.cTitle, "1000")
         If Err.Number <> 0 Then bContinue = False
         On Error GoTo 0
      End If

      If bContinue Then
         bContinue = MsgBox("Each cells of the range " &amp; Selection.Address &amp; " will be initialized with a random number between " &amp; wMin &amp; _
            " and " &amp; wMax &amp; ".  Click on continue to proceed.", vbQuestion + vbYesNo + vbDefaultButton2, CAVO_AddIn.cTitle) = vbYes
      End If

      If bContinue Then

         For Each rng In Selection
            rng.Value = Application.WorksheetFunction.RandBetween(wMin, wMax)
         Next

      End If

   End If ' If (Selection Is Nothing) Or (Selection.Cells.Count < 2) Then

End Sub

' =
' =
'  LINKS FUNCTIONNALITIES 
' =
' =

Sub CAVOXLAddin_enableLinks(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

Sub CAVOXLAddin_enableRanges(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

Sub clickCAVO_LinksBtnGetWBLinks(control As IRibbonControl)

   If (cLinks Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cLinks.List(ActiveWorkbook)

End Sub

' =
' =
'  CONDITIONAL FORMATTING FUNCTIONNALITIES 
' =
' =

Sub CAVOXLAddin_enableCondFormatting(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Highlight cells having a conditional formatting.  Call the function once more to
' restore cells background
'
' ------------------------------------------------------------------------------

Sub clickCAVO_CondFormattingBtnHighLight(control As IRibbonControl)

   If (cFormatting Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cFormatting.HighLightCells(ActiveSheet)

End Sub

' ------------------------------------------------------------------------------
'
' Apply an OK-ERROR conditional formatting to the selection.
' OK cells should contains the zero figure; cells with an error a different value (f.i. -1).
' OK cells will be put in red, ERROR cells will be put in red
'
' ------------------------------------------------------------------------------

Sub clickCAVO_CondFormattingBtnOKError(control As IRibbonControl)

Dim rng As Range

   On Error Resume Next
   Set rng = Selection
   Set rng = Application.InputBox(Prompt:="Select the range that contains the OK / Error indicator", Default:=rng.Address, Type:=8)
   On Error GoTo 0

   If Not (rng Is Nothing) Then
      If (cFormatting Is Nothing) Then Call CAVO_AddIn.Initialize
      Call CAVO_AddIn.cFormatting.OK_Error(rng)
   End If

   Set rng = Nothing

End Sub

' ------------------------------------------------------------------------------
'
' Apply an odd-even conditional formatting to the selection : put one line on two with
' a gray background color.
'
' ------------------------------------------------------------------------------

Sub clickCAVO_CondFormattingBtnOddEven(control As IRibbonControl)

Dim rng As Range

   On Error Resume Next
   Set rng = Selection
   Set rng = Application.InputBox(Prompt:="Select the range for which we wish to put one line on two with a gray background", Default:=rng.Address, Type:=8)
   On Error GoTo 0

   If Not (rng Is Nothing) Then
      If (cFormatting Is Nothing) Then Call CAVO_AddIn.Initialize
      Call CAVO_AddIn.cFormatting.OddEven(rng)
   End If

   Set rng = Nothing

End Sub

' ------------------------------------------------------------------------------
'
' Put in red cells with a duplicate value i.e. with the same value that on
' another cell in the selected range
'
' ------------------------------------------------------------------------------

Sub clickCAVO_CondFormattingBtnDupValues(control As IRibbonControl)

Dim rng As Range

   On Error Resume Next
   Set rng = Selection
   Set rng = Application.InputBox(Prompt:="Select the range that contains the values for which you wish to verify if there are duplicate values", Default:=rng.Address, Type:=8)
   On Error GoTo 0

   If Not (rng Is Nothing) Then
      If (cFormatting Is Nothing) Then Call CAVO_AddIn.Initialize
      Call CAVO_AddIn.cFormatting.DuplicateValues(rng)
   End If

   Set rng = Nothing

End Sub

' ------------------------------------------------------------------------------
'
' Clear conditional formatting
'
' ------------------------------------------------------------------------------

Sub clickCAVO_CondFormattingBtnClear(control As IRibbonControl)

Dim rng As Range

   On Error Resume Next
   Set rng = Selection
   Set rng = Application.InputBox(Prompt:="Select the range that contains the values for which you wish to verify if there are duplicate values", Default:=rng.Address, Type:=8)
   On Error GoTo 0

   If Not (rng Is Nothing) Then rng.FormatConditions.Delete

   Set rng = Nothing

End Sub

' =
' =
'  WORKSHEETS FUNCTIONNALITIES 
' =
' =

Sub CAVOXLAddin_enableSheets(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Send the ActiveSheet by email
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnMail(control As IRibbonControl)

Dim sFileName As String, sName As String, sSubject As String
Dim wb As Workbook, wbSource As Workbook
Dim bAlerts As Boolean

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize
   If (cMail Is Nothing) Then Call CAVO_AddIn.Initialize

   Set wbSource = ActiveWorkbook

   ' Export the active sheet into a new workbook and get back the fullname of the new workbook (f.i. c:\temp\wb_new.xlsx)
   Set wb = cSheet.Export(ActiveSheet, ActiveWorkbook)

   If Not wb.Saved Then

      On Error Resume Next
      wb.BuiltinDocumentProperties("Title") = wbSource.BuiltinDocumentProperties("Title")

      bAlerts = Application.DisplayAlerts
      Application.DisplayAlerts = False

      wb.SaveAs cFolder.GetTempFolderName &amp; ActiveSheet.Name

      Application.DisplayAlerts = bAlerts

      If Err.Number <> 0 Then wb.SaveAs cFile.GetTempFileName(sExtension:="xlsx")

      On Error GoTo 0

   End If

   ' Get the filename

   sFileName = wb.FullName

   On Error Resume Next
   sSubject = wb.BuiltinDocumentProperties("Title")
   If Err.Number <> 0 Then sSubject = cFile.GetFileName(sFileName)
   Err.Clear
   On Error GoTo 0

   Set wbSource = Nothing
   Set wb = Nothing

   ' Prepare a mail with the file in attach

   Call cMail.SendMail(Subject:=sSubject, AttachFileName:=sFileName)

End Sub

' ------------------------------------------------------------------------------
'
' Export the active sheet into a new workbook
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnExport(control As IRibbonControl)

Dim sFileName As String
Dim wb As Workbook

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize

   ' Export the active sheet into a new workbook and get back the fullname of the new workbook (f.i. c:\temp\wb_new.xlsx)
   Set wb = cSheet.Export(arrSh:=ActiveSheet, bRemoveVBAIfPresent:=True)
   sFileName = wb.FullName
   Set wb = Nothing

End Sub

' ------------------------------------------------------------------------------
'
' Show a specific sheet based on his name
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnShowOne(control As IRibbonControl)

Static sSheetName As String

   On Error Resume Next
   sSheetName = InputBox("Please mention the name of the sheet you want to make visible", CAVO_AddIn.cTitle, sSheetName)
   On Error GoTo 0

   If (sSheetName <> vbNullString) Then
      If (CAVO_AddIn.cSheet.Exists(sSheetName, ActiveWorkbook)) Then
         Call CAVO_AddIn.cSheet.Show(Worksheets(sSheetName), ActiveWorkbook)
      Else
         MsgBox "Sheet [" &amp; sSheetName &amp; "] not found", vbExclamation, CAVO_AddIn.cTitle
      End If
   End If

End Sub

' ------------------------------------------------------------------------------
'
' Very hide the active sheet
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnHideOne(control As IRibbonControl)

   Call CAVO_AddIn.cSheet.Hide(ActiveSheet)

End Sub

' ------------------------------------------------------------------------------
'
' Show all sheets
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnShowAll(control As IRibbonControl)

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cSheet.Show

End Sub

' ------------------------------------------------------------------------------
'
' Hide all sheets except active one
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnHideAll(control As IRibbonControl)

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cSheet.Hide(, ActiveSheet)

End Sub

' ------------------------------------------------------------------------------
'
' Compress the active sheet
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetsBtnCompress(control As IRibbonControl)
   If (CAVO_AddIn.cWorkbook Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cSheet.ResetLastUsedCell(ActiveSheet)
End Sub

' ------------------------------------------------------------------------------
'
' Protect the current sheet
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnProtect(control As IRibbonControl, pressed As Boolean)

Static sPassword As String

Dim sQuestion As String

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize

   If CAVO_AddIn.cPassword.Password = vbNullString Then

      If ActiveSheet.ProtectContents Then
         sQuestion = "This sheet is protected.  Please enter the password to use to unprotect it"
      Else
         sQuestion = "Please enter the password to use to protect the sheet"
      End If

      If (sPassword = vbNullString) Then sPassword = InputBox(sQuestion, cTitle, "MySecretPassword")

   Else

      If (cPassword.isObfuscated) Then sPassword = cFunctions.Obfusc(cPassword.Password, False) Else sPassword = cPassword.Password

   End If

   If ActiveSheet.ProtectContents Then
      Call cSheet.Unprotect(ActiveSheet, sPassword)
   Else
      Call cSheet.Protect(ActiveSheet, sPassword)
   End If

   pressed = ActiveSheet.ProtectContents

End Sub

' ------------------------------------------------------------------------------
'
' Unprotect all sheets
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnUnProtectAll(control As IRibbonControl)

Dim sh As Worksheet
Dim sQuestion As String, sPassword As String

   If (cSheet Is Nothing) Then Call CAVO_AddIn.Initialize

   If CAVO_AddIn.cPassword.Password = vbNullString Then

      If ActiveSheet.ProtectContents Then
         sQuestion = "Sheets are protected.  Please enter the password to use to unprotect them"
      Else
         sQuestion = "Please enter the password to use to unprotect sheets"
      End If

      If (sPassword = vbNullString) Then
         CAVO_AddIn.cPassword.Password = InputBox(sQuestion, cTitle, "MySecretPassword")
         CAVO_AddIn.cPassword.isObfuscated = False
      End If

   Else

      If (cPassword.isObfuscated) Then sPassword = cFunctions.Obfusc(cPassword.Password, False) Else sPassword = cPassword.Password

   End If

   For Each sh In ActiveWorkbook.Worksheets
      Call CAVO_AddIn.cSheet.Unprotect(sh)
   Next

   MsgBox "Sheets protection removed", vbInformation, cTitle

End Sub

' ------------------------------------------------------------------------------
'
' Reset the worksheet protection to a null string then unprotect the sheet
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnBreakProtection(control As IRibbonControl)

   With ActiveSheet

      If .ProtectContents Then

         .Protect vbNullString, , True, , , , , , , , , , , , , True
         .Unprotect vbNullString

         If .ProtectContents Then
            Call CAVO_AddIn.cMessage.Show("Toolbar::SheetBtnBreakProtection", "The sheet is still protected", Error)
         Else
            Call CAVO_AddIn.cMessage.Show("Toolbar::SheetBtnBreakProtection", "Protection removed", Good)
         End If
      Else
         Call CAVO_AddIn.cMessage.Show("Toolbar::SheetBtnBreakProtection", "This sheet is not currently protected", info)
      End If ' If .ProtectContents Then

   End With ' With ActiveSheet

End Sub

' ------------------------------------------------------------------------------
'
' Determine, on the worksheet activation, if ranges like '_HiddenCols' or '_HiddenRows' exists
' and if so, if they're displayed or not.
'
' Initialize the global but private bShowHiddenHiddenRanges variable
'
' ------------------------------------------------------------------------------

Sub pressedShowHideHiddenRanges(control As IRibbonControl, ByRef returnedVal)

Dim nm As Name, sName As String

   On Error Resume Next

   bShowHiddenHiddenRanges = False

   ' Retrieve the first _HiddenCols or _HiddenRows range (if present) and return its state = true if the range is visible

   If (Not (ActiveWorkbook) Is Nothing) Then

      For Each nm In ActiveSheet.Names

         sName = nm.Name
         If InStr(sName, "!") > 0 Then sName = Mid(sName, InStr(sName, "!") + 1, Len(sName))

         If (Left(sName, 11) = "_HiddenCols") Then
            bShowHiddenHiddenRanges = Not ActiveSheet.Range(nm.Name).EntireColumn.Hidden
            Exit For
         ElseIf (Left(sName, 11) = "_HiddenRows") Then
            bShowHiddenHiddenRanges = Not ActiveSheet.Range(nm.Name).EntireRow.Hidden
            Exit For
         End If

      Next nm

   End If

   Err.Clear
   On Error GoTo 0

   returnedVal = bShowHiddenHiddenRanges

End Sub

' ------------------------------------------------------------------------------
'
' The ShowHiddenRanges button is a shortcut that will display/hide ranges in the current sheet
' when the range's name start with "_HiddenCols" or "_HiddenRows".
'
' This is a developper helper
'
' ------------------------------------------------------------------------------

Sub clickCAVO_ShowHideHiddenRanges(control As IRibbonControl, pressed As Boolean)

Dim bScreenUpdating As Boolean, bEvents As Boolean
Dim nm As Name, sName As String
Dim bFound As Boolean

   bScreenUpdating = Application.ScreenUpdating
   bEvents = Application.EnableEvents
   Application.ScreenUpdating = False
   Application.EnableEvents = False

   bFound = False

   On Error Resume Next

   For Each nm In ActiveSheet.Names

      sName = nm.Name
      If InStr(sName, "!") > 0 Then sName = Mid(sName, InStr(sName, "!") + 1, Len(sName))

      If (Left(sName, 11) = "_HiddenCols") Then
         bFound = True
         ActiveSheet.Range(nm.Name).EntireColumn.Hidden = Not pressed
         bShowHiddenHiddenRanges = Not pressed
      ElseIf (Left(sName, 11) = "_HiddenRows") Then
         bFound = True
         ActiveSheet.Range(nm.Name).EntireRow.Hidden = Not pressed
         bShowHiddenHiddenRanges = Not pressed
      End If

   Next nm

   ' Invalidate the control so the new label, depending on the range's state, can be refreshed
   Call CAVO_AddIn.ribbon.InvalidateControl("CAVOXLAddin_ShowHideHiddenRanges")

   Err.Clear
   On Error GoTo 0

   If Not bFound Then
      Call cMessage.Show("Toolbar::ShowHideHiddenRanges", "No ranges named with the prefix '_HiddenCols' or '_HiddenRows' has been found", info)
   End If

   Application.EnableEvents = bEvents
   Application.ScreenUpdating = bScreenUpdating

End Sub

' ------------------------------------------------------------------------------
'
' Determine the text (label) for the Show/Hide hidden ranges button; from the "Ranges" group
'
' ------------------------------------------------------------------------------

Sub CAVOXLAddin_getLabelShowHideHiddenRanges(control As IRibbonControl, ByRef returnedVal)

    returnedVal = IIf(bShowHiddenHiddenRanges, "Hide", "Show")

End Sub

' ------------------------------------------------------------------------------
'
' Does the active sheet is protected ?
'
' ------------------------------------------------------------------------------

Sub pressedCAVO_SheetBtnProtect(control As IRibbonControl, ByRef returnedVal)
   If (Application.Workbooks.Count > 0) Then returnedVal = (ActiveSheet.ProtectContents)
End Sub

' -----------------------------------------------------------------------------------
'
' Send the selection (if any) or send the sheet used range by email, the range will be converted
' as HTML and then appended to the mail body
'
' -----------------------------------------------------------------------------------

Private Sub clickCAVO_SheetBtnMailSelection(control As IRibbonControl)

Dim rng As Range

   Set rng = IIf(Selection Is Nothing, ActiveSheet.UsedRange, Selection)

   If (cMail Is Nothing) Then Call CAVO_AddIn.Initialize

   Call CAVO_AddIn.cMail.SendMail(MailBody:=CAVO_AddIn.cRange.toHTML(rng))

   Set rng = Nothing

End Sub

' =
' =
'  FORMULAS FUNCTIONNALITIES =
' =
' =

Sub CAVOXLAddin_enableFormulas(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the number of formulas in the workbook
'
' ------------------------------------------------------------------------------

Sub clickCAVO_FormulasBtnCount(control As IRibbonControl)

   If (CAVO_AddIn.cFormula Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cFormula.CountFormulas(, , False)

End Sub

' ------------------------------------------------------------------------------
'
' Highlight cells having a formulas in it.  Calling the function once will restore
' the cell's background
'
' ------------------------------------------------------------------------------

Sub clickCAVO_FormulasBtnHighlight(control As IRibbonControl)
   If (CAVO_AddIn.cFormula Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.Initialize: Call CAVO_AddIn.cFormula.Highlight(ActiveSheet, vbYellow)
End Sub

' ------------------------------------------------------------------------------
'
' Find circular references in the active workbook
'
' ------------------------------------------------------------------------------

Sub clickCAVO_FormulasBtnFindCircularRef(control As IRibbonControl)
   If (CAVO_AddIn.cFormula Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cFormula.FindCircularReferences(, ActiveWorkbook)
End Sub

' =
' =
'  NAMES FUNCTIONNALITIES =
' =
' =

Sub CAVOXLAddin_enableNames(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve all global names in the active sheet and convert them as local one
' After execution, all names found in the sheet will be with a local scope.
'
' ------------------------------------------------------------------------------

Sub clickCAVO_NamesBtnToggleGlobalToLocal(control As IRibbonControl)

Dim arr() As T_Ranges
Dim i As Long, J As Long
Dim sMsg As String

   Application.Cursor = XlMousePointer.xlWait

   arr = CAVO_AddIn.cName.GetGlobalNames(ActiveSheet.Name, ActiveSheet)

   On Error Resume Next
   J = UBound(arr)
   On Error GoTo 0

   If J > 0 Then
      For i = 1 To J
         Call CAVO_AddIn.cName.ToggleScope(arr(i).Name, ActiveSheet)
         sMsg = sMsg &amp; arr(i).Name &amp; "|"
      Next
      If (Right(sMsg, 1) = "|") Then sMsg = Left(sMsg, Len(sMsg) - 1)
   End If

   Application.Cursor = XlMousePointer.xlDefault

   If sMsg <> vbNullString Then
      MsgBox "Global names have been changed to local ones : " &amp; vbCrLf &amp; vbCrLf &amp; Replace(sMsg, "|", ", "), vbInformation, CAVO_AddIn.cTitle
   Else
      MsgBox "No global names found in " &amp; ActiveSheet.Name, vbInformation, CAVO_AddIn.cTitle
   End If

End Sub

' ------------------------------------------------------------------------------
'
' Show all named range
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_NamesBtnShowAll(control As IRibbonControl)

   If (cName Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cName.Show

End Sub

' ------------------------------------------------------------------------------
'
' Hide all named range
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_NamesBtnHideAll(control As IRibbonControl)

   If (cName Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cName.Hide

End Sub

' ------------------------------------------------------------------------------
'
' Remove every names from the workbook : delete every global names and every
' local ones.   Be sure that it's what you want to do
'
' ------------------------------------------------------------------------------

Sub clickCAVO_NamesBtnRemoveAll(control As IRibbonControl)

   If (cName Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cName.RemoveAllNames(wb:=ActiveWorkbook, bAskConfirmation:=True)

End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the list of all names in the active workbook and output this
' list in a new worksheet
'
' ------------------------------------------------------------------------------

Sub clickCAVO_NamesBtnGetNames(control As IRibbonControl)

   If (cName Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cName.Debug_ListNames(vbNullString, Nothing, Nothing, False)

End Sub

' =
' =
'  DATA CONNECTIONS FUNCTIONNALITIES ==
' =
' =

Sub CAVOXLAddin_enableData(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the list of all data connections in the active workbook and output this
' list in a new worksheet
'
' ------------------------------------------------------------------------------

Sub clickCAVO_DataBtnGetWBConnections(control As IRibbonControl)

   If (cMail Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cData.Debug_DisplayConnections(ActiveWorkbook, False)

End Sub

' ------------------------------------------------------------------------------
'
' Add a new query table in the workbook.
'
' ------------------------------------------------------------------------------

Sub clickCAVO_DataBtnAddQueryTable(control As IRibbonControl)

MsgBox "Not yet coded.   Should display a form to allow encoding query name, login, password, ...   See clickCAVO_DataBtnAddQueryTable sub in the Toolbar module"
'Dim dc As CAVO_AddIn.T_Workbook_Data_Connection
'
'   With dc
'      .Name = "qryGetFigures"
'      .ObfuscSensitiveData = True                 ' False to be able to refresh the table just by right-clicking and then Refresh
'      .Type = Enum_Data_Connection_Type.MSSQL     ' SQL Server connection
'      .PersistSecurityInfo = True                 ' Store the password in the connection string. * BAD idea if not obfuscated *
'      .UserId = "userEBUD_R"                      ' DB Username
'      .Password = "XXXXXXXXXXXXXXXXXXXXXXXXXXX"   ' DB User password
'      .DBName = "eBudget"                         ' Name of the database
'      .ServerName = "sql2k5cl"                    ' Name of the server
'      .CommandText = "EXEC [eXL].[usp_GetFigures] '2013',1, 1, '03','%', '%', '%', '%', '%','%', '%', 1, 'avonture_christophe', 0"
'      Set .TargetRange = Worksheets("Sheet1").Range("$B$4")   ' or simply ActiveCell
'   End With
'
'   Call CAVO_AddIn.cData.AddQueryTable(dc)        ' Do it!   Create the data connection, add the query table and refresh it.
'
End Sub

' ------------------------------------------------------------------------------
'
' Who can click on the "Run SQL" button
'
' ------------------------------------------------------------------------------

Sub CAVOXLAddin_enableBtnRunSQL(control As IRibbonControl, ByRef Enabled)
   If (CAVO_AddIn.cUser Is Nothing) Then
      Enabled = False
   Else
      Enabled = (CAVO_AddIn.cUser.Name = "YOURICT\avonture_christophe")
   End If
End Sub

' ------------------------------------------------------------------------------
'
' Add a new query table in the workbook.
'
' ------------------------------------------------------------------------------

Sub clickCAVO_DataBtnRunSQL(control As IRibbonControl)

Static sReportTitle As String
Static sSQL As String

   With CAVO_AddIn.frmRunSQL
      .edtReportTitle.Value = sReportTitle
      .edtSQL.Value = sSQL

      .Show

      ' Remember the value in case of
      sReportTitle = .edtReportTitle.Value
      sSQL = .edtSQL.Value

      On Error Resume Next
      CAVO_AddIn.ribbon.Invalidate
      Workbooks(.edtFileName.Text).Activate
      On Error GoTo 0

   End With
End Sub

' =
' =
'  MACRO FUNCTIONNALITIES =
' =
' 

Sub CAVOXLAddin_enableVBA(control As IRibbonControl, ByRef Enabled)
   Enabled = (Application.Workbooks.Count > 0)
End Sub

' ------------------------------------------------------------------------------
'
' Click on the BackupCode button : backup the VBA code of the workbook as files
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBABtnBackupCode(control As IRibbonControl)

   If (cMacro Is Nothing) Then Call CAVO_AddIn.Initialize
   Call cMacro.Export

End Sub

' ------------------------------------------------------------------------------
'
' Get the number of lines in the modules, classes, ... in the VB code
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBABtnCountLines(control As IRibbonControl)

   If (cMacro Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cMacro.CountLines(, False)

End Sub

' ------------------------------------------------------------------------------
'
' Loop all addins and if one is found, try to replace the folder name of that addin
' to an another one
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBABtnReplaceReference(control As IRibbonControl)

Dim ref As Reference
Dim sSourcePath As String, sTargetPath As String
Dim objFSO As Object
Dim arr() As Variant
Dim i As Byte
Dim wCount As Byte

   On Error Resume Next

   Set objFSO = CreateObject("Scripting.FileSystemObject")

   For Each ref In ActiveWorkbook.VBProject.References
      If (ref.GUID = vbNullString) Then
        sSourcePath = objFSO.GetParentFolderName(ref.FullPath)
        Exit For
      End If
   Next

   sSourcePath = InputBox("Loop all addin's references; change the addin's location from [SourceFolder] to [TargetFolder]." &amp; vbCrLf &amp; vbCrLf &amp; "Mention the [SourceFolder]", cTitle, sSourcePath)

   If (sSourcePath <> vbNullString) Then

      sTargetPath = InputBox("Mention the [TargetFolder]", cTitle, ActiveWorkbook.Path &amp; "\")

      If (sTargetPath <> vbNullString) Then

         If Not (Right(sSourcePath, 1) = "\") Then sSourcePath = sSourcePath &amp; "\"
         If Not (Right(sTargetPath, 1) = "\") Then sTargetPath = sTargetPath &amp; "\"

         i = 0

         For Each ref In ActiveWorkbook.VBProject.References

            If (ref.GUID = vbNullString) Then

              If (InStr(ref.FullPath, sSourcePath) > 0) Then

                 ' One reference addin found having and stored in [SourcePath]
                 ' Prepare the array that will be used by ReplaceReferencesAddIns to replace this path by [TargetPath]

                 i = i + 1
                 ReDim Preserve arr(i)

                 arr(i) = Array(ref.Name, Replace(ref.FullPath, sSourcePath, sTargetPath))

              End If

            End If ' If (ref.GUID = vbNullString ) Then

         Next

      End If ' If (sTargetPath <> vbNullString ) Then

      If (cMacro Is Nothing) Then Call CAVO_AddIn.Initialize
      wCount = CAVO_AddIn.cMacro.ReplaceReferencesAddIns(arr, ActiveWorkbook)

      If (wCount > 0) Then MsgBox (wCount &amp; " references have been updated to " &amp; sTargetPath)

   End If ' If (sSourcePath <> vbNullString ) Then

End Sub

' ------------------------------------------------------------------------------
'
' Get the list of VB references (.xlam, .dll, ...) used by the workbook
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBABtnGetReferences(control As IRibbonControl)

   If (cMacro Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cMacro.GetReferences(ActiveWorkbook, False)

End Sub

' ------------------------------------------------------------------------------------------------------------
'
' Toggle the "isAddIn" property of loaded addin(s)
'
' ------------------------------------------------------------------------------------------------------------

Private Sub clickCAVO_VBABtnToggleIsAddIn(control As IRibbonControl)

   If (cMacro Is Nothing) Then Call CAVO_AddIn.Initialize
   Call CAVO_AddIn.cMacro.ToggleIsAddIn

End Sub

' ------------------------------------------------------------------------------
'
' Click on the Enable Events button : enable / disable events
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBAEnableEvents(control As IRibbonControl, pressed As Boolean)
   Application.EnableEvents = pressed
End Sub

' ------------------------------------------------------------------------------
'
' Click on the Debug Mode button : enable / disable debugmode
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBADebugMode(control As IRibbonControl, pressed As Boolean)
   CAVO_AddIn.cDebug.Enabled = pressed
End Sub

' ------------------------------------------------------------------------------
'
' Click on the Full Debug Mode button : enable / disable debugmode
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_VBAFUllDebugMode(control As IRibbonControl, pressed As Boolean)
   CAVO_AddIn.cDebug.EnabledFullDebug = pressed
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the state of the Enable events toggle button; it's depending on
' Application.EnableEvents
'
' ------------------------------------------------------------------------------

Sub pressedCAVO_VBAEnableEvents(control As IRibbonControl, ByRef pressed)
   pressed = Application.EnableEvents
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the state of the Debug mode toggle button
'
' ------------------------------------------------------------------------------

Sub pressedCAVO_VBADebugMode(control As IRibbonControl, ByRef pressed)
   If (CAVO_AddIn.cDebug Is Nothing) Then Call CAVO_AddIn.Initialize(True)
   pressed = CAVO_AddIn.cDebug.Enabled
End Sub

' ------------------------------------------------------------------------------
'
' Retrieve the state of the Full Debug mode toggle button
'
' ------------------------------------------------------------------------------

Sub pressedCAVO_VBAFullDebugMode(control As IRibbonControl, ByRef pressed)
   If (CAVO_AddIn.cDebug Is Nothing) Then Call CAVO_AddIn.Initialize(True)
   pressed = CAVO_AddIn.cDebug.EnabledFullDebug
End Sub

' =
' =
'  MISC FUNCTIONNALITIES =
' =
' =

' ------------------------------------------------------------------------------
'
' Click on the Crypt button, provide an easy way to crypt / uncrypt a keyword
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_MiscBtnCrypt(control As IRibbonControl)

Dim sKeyWord As String, sCrypt As String
Dim bIsEncrypted As Boolean
Dim wb As Workbook

   If (cFunctions Is Nothing) Then Call CAVO_AddIn.Initialize

   On Error Resume Next
   sKeyWord = Trim(InputBox("Please type the keyword to crypt or the encrypted text", cTitle, "Your keyword"))
   On Error GoTo 0

   If (sKeyWord = vbNullString) Or (sKeyWord = "Your keyword") Then Exit Sub

   bIsEncrypted = Not (MsgBox("Click on Yes if the value [" &amp; sKeyWord &amp; "] is not yet encrypted; " &amp; _
      "click on No if this is already an encrypted value", vbQuestion + vbYesNo, cTitle) = vbYes)

   Set wb = Application.Workbooks.Add

   On Error Resume Next

   With wb.Worksheets(1)

      .Cells(1, 1).Value = "Crypted"

      If (bIsEncrypted) Then
         sCrypt = sKeyWord
      Else
         sCrypt = CAVO_AddIn.cFunctions.Obfusc(sKeyWord, True)
      End If

      .Cells(1, 2).Value = sCrypt

      .Cells(2, 1).Value = "Uncrypted"
      .Cells(2, 2).Value = CAVO_AddIn.cFunctions.Obfusc(sCrypt, False)

   End With

   On Error GoTo 0

End Sub

' =
' =
'  BPC ==
' =
' =

' ------------------------------------------------------------------------------
'
' BPC - Show/Hide EPMFormattingSheets - Get the current state so the toolbar can
' display the button On or Off
'
' ------------------------------------------------------------------------------

Sub pressedShowHideBPCFormattingSheets(control As IRibbonControl, ByRef returnedVal)

'Dim sh As Worksheet
'
'   If (ActiveWorkbook Is Nothing) Then Exit Sub
'   If (ActiveWorkbook.Worksheets.Count = 0) Then Exit Sub
'
'   For Each sh In ActiveWorkbook.Worksheets
'
'      If (Left(sh.Name, Len("EPMFormattingSheet")) = "EPMFormattingSheet") Then
'         bShowHiddenBPCFormattingSheets = (sh.visible = xlSheetVisible)
'         Exit For
'      End If
'
'   Next
'
'   returnedVal = bShowHiddenBPCFormattingSheets

End Sub

' ------------------------------------------------------------------------------
'
' Determine the text (label) for the Show/Hide BPC Formatting sheets; from the "BPC" group
'
' ------------------------------------------------------------------------------

Sub CAVOXLAddin_getLabelShowHideBPCFormattingSheets(control As IRibbonControl, ByRef returnedVal)

    returnedVal = IIf(bShowHiddenBPCFormattingSheets, "Hide", "Show")

End Sub

' ------------------------------------------------------------------------------
'
' Show/Hide BPC EPMFormatting sheets
'
' This is a developper helper
'
' ------------------------------------------------------------------------------

Sub clickCAVO_ShowHideBPCFormattingSheets(control As IRibbonControl, pressed As Boolean)

'Dim bScreenUpdating As Boolean, bEvents As Boolean
'Dim sh As Worksheet
'
'   bScreenUpdating = Application.ScreenUpdating
'   bEvents = Application.EnableEvents
'   Application.ScreenUpdating = False
'   Application.EnableEvents = False
'
'   On Error Resume Next
'
'   For Each sh In ActiveWorkbook.Worksheets
'
'      If (Left(sh.Name, Len("EPMFormattingSheet")) = "EPMFormattingSheet") Then
'         sh.visible = IIf(pressed, xlSheetVisible, xlSheetHidden)
'         bShowHiddenBPCFormattingSheets = Not bShowHiddenBPCFormattingSheets
'      End If
'
'   Next
'
'   ' Invalidate the control so the new label, depending on the range's state, can be refreshed
'   Call CAVO_AddIn.ribbon.InvalidateControl("CAVOXLAddin_BPC_ShowHideEPMFormattingSheet")
'
'   Err.Clear
'   On Error GoTo 0
'
'   Application.EnableEvents = bEvents
'   Application.ScreenUpdating = bScreenUpdating

End Sub

' ------------------------------------------------------------------------------
'
' Display the EPM Log
'
' This is a developper helper
'
' ------------------------------------------------------------------------------

Sub clickCAVO_BPC_ShowLog(control As IRibbonControl)

'   Call CAVO_AddIn.cEPM.DisplayLog

End Sub

' ------------------------------------------------------------------------------
'
' Determine if the buttons of the BPC group can be enabled or not
'
' ------------------------------------------------------------------------------

Sub enableBPCButtons(control As IRibbonControl, ByRef Enabled)

'   If (ActiveSheet Is Nothing) Then
'      Enabled = False
'   Else
'      If (cEPM Is Nothing) Then Set cEPM = New CAVO_AddIn.clsEPM
'      cEPM.ConnectionName = Constants.cBPCConnectionName
'      If (control.ID = "CAVOXLAddin_BPC_Connect") Then
'         Enabled = True
'      Else
'         Enabled = CAVO_AddIn.cEPM.IsConnected()
'      End If
'   End If

End Sub

' ------------------------------------------------------------------------------
'
' Connect on / disconnect from EPM
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_BPC_Connect(control As IRibbonControl)

'   If (CAVO_AddIn.cEPM Is Nothing) Then Set CAVO_AddIn.cEPM = New clsEPM
'
'   If Not (CAVO_AddIn.cEPM.IsConnected) Then
'
'      Call CAVO_AddIn.cEPM.Connect(Constants.cBPCLogin, Constants.cBPCPassword, Constants.cBPCConnectionName)
'
'   Else
'      Call CAVO_AddIn.cEPM.Disconnect
'   End If

End Sub

' ------------------------------------------------------------------------------
'
' Determine the text (label) for the Show/Hide hidden ranges button; from the "Ranges" group
'
' ------------------------------------------------------------------------------

Sub CAVOXLAddin_getLabelBPC_Connect(control As IRibbonControl, ByRef returnedVal)

'Dim bSilent As Boolean
'
'   bSilent = cSettings.SilentMode
'   cSettings.SilentMode = True
'
'   If (CAVO_AddIn.cEPM Is Nothing) Then Set CAVO_AddIn.cEPM = New clsEPM
'
'   returnedVal = IIf(CAVO_AddIn.cEPM.IsConnected, "Logout", "Connect")
'
'   cSettings.SilentMode = bSilent

End Sub

' ------------------------------------------------------------------------------
'
' Refresh the current BPC report
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_BPC_Refresh(control As IRibbonControl)

'   If (cEPM Is Nothing) Then Set cEPM = New CAVO_AddIn.clsEPM
'
'   cEPM.ConnectionName = Constants.cBPCConnectionName
'   If Not cEPM.IsConnected Then Call cEPM.Connect(Constants.cBPCLogin, Constants.cBPCPassword)
'
'   If (cEPM.IsConnected) Then CAVO_AddIn.cEPM.Refresh

End Sub

' ------------------------------------------------------------------------------
'
' Extract a dimension will all it's attributes and members.  Create a new worksheet in the active workbook
'
' ------------------------------------------------------------------------------

Private Sub clickCAVO_BPC_ExtractDimension(control As IRibbonControl)

'Dim sDimensionName As String
'Dim shTarget As Worksheet
'Dim wb As Workbook
'Dim arr As Variant
'Dim i As Byte, J As Byte, K As Byte
'Dim bAlerts As Boolean
'Dim wRow As Long
'Dim wCalcMode As XlCalculation
'
'   wCalcMode = Application.Calculation
'   Application.Calculation = xlCalculationManual
'
'   arr = Array("AUDITTRAIL", "CATEGORY", "CODE_ECO", "CODE_FIN", "CRED_SORT", _
'      "CRED_TYP", "CYCLE", "DATA_SOURCE", "ENTITY", "FLOW", "GL_ACCOUNT", "INPUTCURRENCY", "INTERCO", "LITTERA", "O_ACCOUNT", _
'      "R_ACCOUNT", "R_ENTITY", "RPTCURRENCY", "SCOPE", "TIME")
'
'   If (cEPM Is Nothing) Then Call CAVO_AddIn.Initialize
'
'   i = 0
'   While i = 0
'      i = CAVO_AddIn.cFunctions.askUserInput_Int("Which model should you extract ? Press 1 for BUD_MON_INST, 2 for CONSO and 3 for Ownership", 1)
'      If (i <> 1) And (i <> 2) And (i <> 3) Then i = 0
'   Wend
'
'   Select Case i
'      Case 1: cEPM.ConnectionName = Constants.cBPCConnectionName
'      Case 2: cEPM.ConnectionName = Constants.cBPCConnectionName_CONSO
'      Case 3: cEPM.ConnectionName = Constants.cBPCConnectionName_Ownership
'   End Select
'
'   If cEPM.IsConnected Then Call cEPM.Disconnect
'
'   If Not cEPM.IsConnected Then Call cEPM.Connect(Constants.cBPCLogin, Constants.cBPCPassword, cEPM.ConnectionName)
'
'   If (cEPM.IsConnected) Then
'
'      ' The activesheet should contains an existing BPC report (there should be a connection to BPC)
'
'      cEPM.ConnectionString = cEPM.GetConnectionString(ActiveSheet)
'
'      Call CAVO_AddIn.cApp.Status("Dimension(s) is/are being exported.  THIS CAN BE A LONG PROCESS... Please wait")
'
'      Set wb = Application.Workbooks.Add
'
'      J = LBound(arr)
'      K = UBound(arr)
'
'      For i = J To K
'
'         ' The dimension is perhaps not part of the current model
'         If (CAVO_AddIn.cEPM.IsDimensionExists(arr(i))) Then
'
'            Set shTarget = CAVO_AddIn.cSheet.Add(arr(i), wb)
'
'            Call cEPM.ExtractDimension(arr(i), shTarget)
'
'            shTarget.Cells(1, 1).AutoFilter
'            shTarget.Cells(2, 1).Select
'            ActiveWindow.FreezePanes = True
'
'            ' Insert three new rows
'            shTarget.Rows("1:3").Insert Shift:=xlDown
'
'            shTarget.Cells(1, 1).Value = "Exraction date"
'            shTarget.Cells(1, 2).Value = "'" &amp; Format(Now(), "dd/mm/yyyy hh:mm")
'
'            shTarget.Cells(2, 1).Value = "Connection"
'            shTarget.Cells(2, 2).Value = cEPM.ConnectionName
'
'            Call CAVO_AddIn.cRange.Autofit(shTarget)
'            Call CAVO_AddIn.cWindow.NiceDisplay(shTarget)
'
'            On Error Resume Next
'
'            With shTarget.PageSetup
'               .CenterFooter = "&amp;""-,Bold""&amp;14" &amp; cEPM.ConnectionName
'               .RightFooter = "Page &amp;P/&amp;N"
'               .Orientation = XlPageOrientation.xlLandscape
'               .FitToPagesWide = 1
'               .FitToPagesTall = 999
'               .PrintTitleRows = "$1:$4" &amp; wRow
'               .Zoom = False
'            End With
'
'            If Err.Number <> 0 Then Err.Clear
'
'            On Error GoTo 0
'
'         End If ' If (CAVO_AddIn.cEPM.IsDimensionExists(arr(I))) Then
'
'      Next i
'
'      On Error Resume Next
'      If CAVO_AddIn.cSheet.Exists("Sheet1", wb) Then Call CAVO_AddIn.cSheet.Delete("Sheet1", wb)
'      On Error GoTo 0
'
'      wb.Activate
'      wb.Worksheets(1).Select
'
'      ' Save the workbook
'
'      bAlerts = Application.DisplayAlerts
'      Application.DisplayAlerts = False
'
'      wb.SaveAs cFolder.GetTempFolderName &amp; cEPM.ConnectionName
'      If Err.Number <> 0 Then wb.SaveAs cFile.GetTempFileName(sExtension:="xlsx")
'
'      Application.DisplayAlerts = bAlerts
'
'      ' Almost finished
'
'      Call CAVO_AddIn.cApp.Status(vbNullString)
'
'      Set shTarget = Nothing
'      Set wb = Nothing
'
'      Application.Calculation = wCalcMode
'
'   End If ' If (cEPM.IsConnected) Then

End Sub

' =
' =
'  ABOUT ==
' =
' =

Private Sub clickCAVO_ShowAbout(control As IRibbonControl)
   MsgBox cTitle, vbInformation + vbOKOnly, cTitle
End Sub