' vbaJPMToolbar_Excel: Contains VBA routines for customizing toolbars/menus in Excel

' Implementation Notes:

'    * We check for (and, if necessary, create) a complete JPM Custom toolbar automatically at startup.
'      >> (see the Workbook_Open() routine in the ThisWorkbook object) <<
'      However, if you want to add/update the tool bar, you can run these routines "by hand".

'    * The custom toolbar name is captured below, as a constant.
Const cJPMCustomToolbarName = "JPM Custom"  ' >>> Note <<< If you ever want to change the custom toolbar name,
                                            '              run the DeleteJPMCustomToolbar routine "by hand"
                                            '              before changing the name.
' 10/21/2001 jpm : First version - lifted pretty much intact from a set of worksheet-based
'                  routines direct from the internet - www.j-walk.com
' 11/04/2001 jpm : Rewritten to match the 100% VBA-generated style of the Outlook / Word versions
' 11/23/2001 jpm : Converting to a standard "JPM Custom" toolbar for all Office apps. This stems
'                  from all the hassles that Outlook was giving me, but it also seems apparent
'                  the object model(s) that 'tool bars" is the way to think of this stuff.
'                  Also, want to get completely away from customizing any of the default / standard
'                  Office toolbars / menus.

'   ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----   
'   Copyright (c) 2001 James P. MacLennan   All Rights Reserved
'   Questions? Comments? Suggestions? Let me know ... www.cazh1.com
   
'   This program is free software; you can redistribute it and/or modify
'   it under the terms of the GNU General Public License as published by
'   the Free Software Foundation; either version 2 of the License, or
'   (at your option) any later version.
'
'   This program is distributed in the hope that it will be useful,
'   but WITHOUT ANY WARRANTY; without even the implied warranty of
'   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'   GNU General Public License for more details.
'   ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----   

Option Explicit

Sub DeleteJPMCustomToolbar()
' Run this guy to nuke your custom toolbar

   On Error Resume Next
   Application.CommandBars(cJPMCustomToolbarName).Delete
   On Error GoTo 0

End Sub

Sub CheckJPMCustomToolbar()
' At startup time, we check for our custom toolbar. If we don't find it, we install it.
' We also make sure that it's visible

   Dim oCheckBar As CommandBar
   Dim bCustomExists As Boolean
   Dim bCustomVisible As Boolean

' Call DeleteJPMCustomToolbar

   bCustomExists = False
   bCustomVisible = False

   For Each oCheckBar In Application.CommandBars
      If oCheckBar.Name = cJPMCustomToolbarName Then
         bCustomExists = True
         bCustomVisible = oCheckBar.Visible
      End If
   Next oCheckBar

   ' Fix what's not right
   If Not bCustomExists Then Call CreateJPMCustomToolbar
   If Not bCustomVisible Then Application.CommandBars(cJPMCustomToolbarName).Visible = True

End Sub

Sub CreateJPMCustomToolbar()
'  Create standard set of custom Excel menus/toolbars for JPM routines

   Dim oCheckBar As CommandBar
   Dim oNewCommandBar As CommandBar
   Dim oNewMenu As CommandBarPopup
   Dim oNewButton As CommandBarControl

   ' Quick sanity check - don't run this routine if the target toolbar exists
   For Each oCheckBar In Application.CommandBars
      If oCheckBar.Name = cJPMCustomToolbarName Then
         MsgBox "Wait a sec - the " & cJPMCustomToolbarName & " toolbar already exists!", vbOKOnly + vbCritical, "Error"
         Exit Sub
      End If
   Next oCheckBar

   ' Add the custom toolbar
   With Application.CommandBars
      Set oNewCommandBar = .Add
      oNewCommandBar.Enabled = True
      oNewCommandBar.Name = cJPMCustomToolbarName
      oNewCommandBar.Position = msoBarTop
      oNewCommandBar.Visible = True
      Set oNewCommandBar = Nothing
   End With

' === Up to this point, it's pretty standard code across all MS applications ===
'     Now, we are adding menus, buttons, etc. specific to each app.
'     Note that the order of the paragraphs below determine the order of the items on your toolbar

   ' Add a submenu for your commonly called spreadsheets
   With Application.CommandBars(cJPMCustomToolbarName)
      Set oNewMenu = .Controls.Add(Type:=msoControlPopup)
      oNewMenu.Caption = "Favorites"
      oNewMenu.DescriptionText = "Commonly used workbooks"
      oNewMenu.Enabled = True
      oNewMenu.TooltipText = "Commonly used workbooks"
      oNewMenu.Visible = True
      Set oNewMenu = Nothing
   End With

      ' Add menu items to the Favorites submenu
      ' Note that menu items are added in the order listed, top to bottom
      With Application.CommandBars(cJPMCustomToolbarName).Controls("Favorites").Controls
         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = True
         oNewButton.Caption = "&Office ToDo List"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument01"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "&Home ToDo List"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument02"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "&General Matters"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument03"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "&Best Practices"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument04"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "&Password Tracking"
         oNewButton.Enabled = True
         oNewButton.FaceId = 225
         oNewButton.OnAction = "LoadDocument05"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "G&oals and Projects"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument06"
         oNewButton.Style = msoButtonIconAndCaption

         Set oNewButton = .Add(Type:=msoControlButton)
         oNewButton.BeginGroup = False
         oNewButton.Caption = "&Name Lists"
         oNewButton.Enabled = True
         oNewButton.FaceId = 263
         oNewButton.OnAction = "LoadDocument07"
         oNewButton.Style = msoButtonIconAndCaption

      End With

   ' More simple buttons
   With Application.CommandBars(cJPMCustomToolbarName)
      Set oNewButton = .Controls.Add(Type:=msoControlButton)
      oNewButton.BeginGroup = True
      oNewButton.Caption = "Face IDs"
      oNewButton.DescriptionText = "Display Face IDs"
      oNewButton.Enabled = True
      oNewButton.FaceId = 607
      oNewButton.OnAction = "ShowFaceIDsDialog"
      oNewButton.Style = msoButtonIcon
      oNewButton.TooltipText = "Display Face IDs"

    '     Set oNewButton = .Add(Type:=msoControlButton)
    '     oNewButton.BeginGroup = False
    '     oNewButton.Caption = "&Unhide JPM Personal.XLS"
    '     oNewButton.Enabled = True
    '     oNewButton.FaceId = 0
    '     oNewButton.OnAction = "DisplayJPMPersonal"
    '     oNewButton.Style = msoButtonIconAndCaption

      Set oNewButton = .Controls.Add(Type:=msoControlButton)
      oNewButton.BeginGroup = True
      oNewButton.Caption = "About"
      oNewButton.DescriptionText = "About ..."
      oNewButton.Enabled = True
      oNewButton.FaceId = 487
      oNewButton.OnAction = "AboutDialog"
      oNewButton.Style = msoButtonIcon
      oNewButton.TooltipText = "About ..."
   End With

   Set oNewButton = Nothing

End Sub