Option Explicit

Private Sub Workbook_AddInInstall()
'Adds procdures to the Utils Menu
'ARB           ~ 09/28/2004 ~~
'       Changes- 10/20/2006 --
'       Changes- 06/15/2007 -- chg help

    Dim objCmdBrPp      As CommandBarPopup
    Dim objCmdBtn       As CommandBarButton
    Dim myCustom        As CommandBarControl
    Dim MenuItem        As CommandBarControl
    Dim SubMenuItem     As CommandBarButton
    Dim cbcMenuBar      As CommandBar
    Dim iHelpIndex      As Integer

    Set cbcMenuBar = Application.CommandBars("Worksheet Menu Bar")
    iHelpIndex = cbcMenuBar.Controls("Help").Index

    Set myCustom = cbcMenuBar.Controls. _
                   Add(Type:=msoControlPopup, Before:=iHelpIndex)
'       In XL versions < 2007 put it left of Help
    With myCustom
        .Caption = "&Utilities"                 'Toolbar Title
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "&Mass Text to Columns"  'toolbar text
            .OnAction = "MassText2Columns"      'the macro
            .FaceId = 99                        'Toolbar picture
            .BeginGroup = True
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "A&dd Ticks"
            .OnAction = "AddTicks"
            .FaceId = 30
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Landing &Zone"      'Select cell A1 in every sheet
            .OnAction = "LandingZone"
            .FaceId = 498
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Copy All Paste Values"
            .OnAction = "CopyAllPasteValues"
            .FaceId = 107
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "UnHide All"
            .OnAction = "UnHideAllSheets"
            .FaceId = 9527
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "&Fix Negatives"
            .OnAction = "FixNegativeValues"
            .FaceId = 1408
        End With
         With .Controls.Add(Type:=msoControlButton)
            .Caption = "Split Product and &Pack"
            .OnAction = "StripCaseToCol"
            .FaceId = 349
        End With
         With .Controls.Add(Type:=msoControlButton)
            .Caption = "Concantenate Cells"
            .OnAction = "ConCantenator"
            .FaceId = 9246
        End With
         With .Controls.Add(Type:=msoControlButton)
            .Caption = "Fill Down in Pivot"
            .OnAction = "FillPivot"
            .FaceId = 1145      '09/07/2005
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Delete T:U"
            .OnAction = "ZapColumns"
            .FaceId = 90
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "List Formulas"
            .OnAction = "ListFormulas"
            .FaceId = 855
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "List Environs()"
            .OnAction = "EnvironListing"
            .FaceId = 856
        End With
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
            With MenuItem
                .Caption = "Col&or Things"
                .BeginGroup = True
            End With
        'First SubMenu
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
      
        With SubMenuItem
            .Caption = "Colo&r Rows or Cols"
            .OnAction = "ColorRows"
            .FaceId = 1763
            .BeginGroup = True
            .TooltipText = "Color your rows or columns with alternating color and white"
        End With
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Color Num&bers"
            .OnAction = "GetColor"      'Give me back color numbers
            .FaceId = 643               'to sort on color
            .TooltipText = "Get the number for the color in column one"
        End With
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Col&umn Differences"
            .OnAction = "ColorColumnDifferences"
            .FaceId = 105
            .TooltipText = "Highlight Column to Start"
        End With
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "H&ighlight Un-protected Cells"
            .OnAction = "ColorUnProtected"
            .FaceId = 417
        End With
'End color utils
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "&Blanks to Zero"
            .OnAction = "FillUsedBlanksWithZero"
             .BeginGroup = True
            .FaceId = 70
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "&Dice Game"
            .OnAction = "DiceGamer"     'Borrowed from Walkenbach
            .FaceId = 6529
        End With
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
            With MenuItem
                .Caption = "UP&C"
                .BeginGroup = True
            End With
        'First SubMenu
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        
        With SubMenuItem
            .Caption = "Shor&ten UPC Width"
            .OnAction = "FixUPCLength"  'Chg 11 or 12 digit UPC to standard 10
            .FaceId = 9678
        End With
        'Second SubMenu
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Pad UPC to 10 digits"
            .OnAction = "Text2NumericUPC"
            .FaceId = 3096
            'Padded to 10 places
        End With
        'Third SubMenu
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = " &UPC Conversion"  'Adds col, shortens to 10 digits
            .OnAction = "BudNetUPCConversion"
            .FaceId = 2601
        End With

        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
            With MenuItem
                .Caption = "W&orksheet Options"
                .BeginGroup = True
            End With
        'First SubMenu
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        
        With SubMenuItem
            .Caption = "B&reak Sheet into Tabs"
            .OnAction = "BreakStoresIntoTabs"
            .BeginGroup = True
            .FaceId = 2556
        End With
        '2nd Sub
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "T&wist Stores from Top"
            .OnAction = "TwistAndShout"     'Melissa's stores
            .FaceId = 9143
        End With
        '3rd Sub
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Combine Ta&bs into One Sheet"
            .OnAction = "CombineIntoOne"
            .FaceId = 1548
        End With
                '4th Sub
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Eac&h Tab to New Workbook"
            .OnAction = "NewBookBreak"
            .FaceId = 9135
        End With
                        '5th Sub
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        With SubMenuItem
            .Caption = "Combine Rows 1-2 Headings"
            .OnAction = "BudNetHeading"
            .FaceId = 582
        End With
        'End Sub Menus
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Insert Date in Cell"
            .OnAction = "PopUpCalendar"
            .FaceId = 1106
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Help"
            .OnAction = "GetVersionInfo"
            .FaceId = 1089
            .BeginGroup = True
        End With
    End With

End Sub

Private Sub Workbook_AddinUninstall()
' This procedure deletes an item on the Tools
' menu on the worksheet menu bar.

' If the Project command exists, delete it.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar") _
            .Controls("&Utilities").Delete
End Sub