Barhopper -- fixup for Restored Toolbars,
    and -- Listing of Menu Items

Location: http://www.mvps.org/dmcritchie/excel/barhopper.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Repopulate personal macro assignments within toolbars and menus

Following a restore of the .XLB file the macro assignments no longer work even if the change is to name the same library and macro for the assignment.

There are two macros shown on this page.  The Barhopper macro starts of the recursive BarHop macro.

The barhopper macro will pick out toolbars containing “Tool” as part of the name and which are not builtin menus, and will call the barhop macro. 

When running this macro suggest opening up the intermediate window in the Visual Basic Editor, under the View menu (or use Ctrl+G).  Note the iteration number in the macro is the level within the menus so sufficient information can be used to make similar macros to take an existing toolbar menu and place it into a spreadsheet for use in John Walkenbach’s (Tip 53) “Menu Maker”. 

The BarHop macro is extracted from
http://www.cit.ctu.edu.vn/daotao/Book/ACCESS97/COURSE/MOD04-11.HTM (dead link), where you will find additional information.  It is part of course materials for MS Access (actually).  You can compare that with Bill Manville’s recursive coding to fix toolbar menus and buttons (Custom Toolbars, 1998/01/17), which has a better interface to start off with -- specify from/to personal libraries. Missed the following on Stephen Bullen’s MVP page will have to check out:  John Green’s CBList (Command Bar List, 15 June 1999) 1-Command Bar Controls (3781 rows), 2-Built-In Button Faces (590 buttons), 3-PopUp CommandBars (52 rows)

One thing missing from BarHopper (this page), and Functions and Macros (BuildToc page is the short cut key assigned to a macro.

I actually use a different filename for my personal.xls file, so you should check that I changed them for this example and that they match what you use.

Following running these macros your toolbars will still not function, you have to then recycle Excel so that your changed toolbars get saved.  You may have to experiment with unhiding/hiding your personal.xls file during the running of the macro.  The problem as reported by Excel is that there are two personal.xls files open.

The code shown below is available here, but more important is the code for a worksheet version with further enhancements.  The code to create and fill in a worksheet was designed for use before Excel 2007, but it also works on the QAT (Quick Access Toolbar) for Excel 2007.  For more information on Excel 2007 see Excel portion of my page on “Vista Basic Home”, which is part of my edition conversion to Windows Vista and to Excel 2007 on a new laptop.

Option Explicit
Sub barhopper()
  Dim cmdBarx As CommandBar
  Dim i As Long
  For Each cmdBarx In CommandBars
    If Not cmdBarx.BuiltIn Then
      If InStr(1, cmdBarx.Name, "My") Or InStr(1, cmdBarx.Name, "Toolbar") Then
       Debug.Print "================"
       Debug.Print cmdBarx.Name
       Debug.Print "================"
       BarHop cmdBarx
      End If
    End If
  Next cmdBarx
End Sub
Sub BarHop(cmdBar As CommandBar, Optional iteration As Variant)
  '--CommandBarControls Collection
  '--http://www.cit.ctu.edu.vn/daotao/Book/ACCESS97/COURSE/MOD04-11.HTM
  '--After replacements (even if they are same as before, are not
  '-- effective until Excel has been completely recycled.
  '-- are not useable until Excel has been recycled, for toolbars to be savedook themre' Debug.Print String$(iteration * 5 + 2, " "); "->"; ctl.OnAction

  Dim ctl
  Dim aMacro As String
  Dim j As Long
  If IsMissing(iteration) Then iteration = 0
  For Each ctl In cmdBar.Controls
    Debug.Print String$(iteration * 5, " "); ctl.Caption
    'Menus with a control type of msoControlPopup will
    'have sub menus that you will run through
    If ctl.Type = msoControlPopup Then
       'If it has a sub-menu, call your routine recursively,
       'passing the command bar object for that control
       BarHop ctl.CommandBar, iteration + 1
    Else
      'REENTRY --- of macros on buttons and menus
      j = InStr(1, ctl.OnAction, "personal.xls'!")
      If j > 0 Then
           aMacro = "personal.xls!" & Mid(ctl.OnAction, j + 13)
           Debug.Print String$(iteration * 5 + 2, " "); "--"; aMacro
           ctl.OnAction = aMacro
          ' Debug.Print String$(iteration * 5 + 2, " "); "->"; ctl.OnAction
      End If
    End If
  Next ctl
End Sub

The Intermediate Window (#ctrlG)

The view in the Intermediate Window (Ctrl+G) shows indentation based on level within the toolbar.  The macro can be started by typing Barhopper into the immediate window and hitting Enter.  The Debug.Print statements provide the following listing of my toolbars.
================
Toolbar 1
================
Footer, custom
  --personal.xls!PutFilenameInFooter
Custom-1
Custom-2
custom[3]
custom-4
  --personal.xls!barhopper
MacroDialogBox
  --personal.xls!MacroDialogBox
&Custom Button
  --personal.xls!RunSubFromActiveCell
GoToSub
  --personal.xls!GoToSub
backupBYDATE
  --personal.xls!backupBYDATE
================
Toolbar 2
================
Sort &Ascending
&Custom Button
  --personal.xls!Euro_Format
================
Toolbar 3
================
**Insert Row**
  --personal.xls!InsertRowsAndFillFormulas
**Top of Column**
  --personal.xls!GotoTopOfCurrentColumn
&Custom Button
  --personal.xls!GotoBottomOfCurrentColumn
GoToPrevSheet
  --personal.xls!GoToPrevSheet
GoToNextSheet
  --personal.xls!GoToNextSheet
MakeHTML_Link
  --personal.xls!MakeHTML_Link
ListFunctionsAndSubs
  --personal.xls!ListFunctionsAndSubs
DeleteThisSheet
  --personal.xls!DeleteThisSheet
================
Toolbar 4
================
================
Toolbar x -- Icon Storehouse
================
&Custom Button
================
My Tools saved menu
================
My Tools
     Char Map
       --personal.xls!showCharMap
     Deletions -- Destructive
          Convert Hyperlinks
            --personal.xls!ConvertHyperlinks
          DelHyperLinks
            --personal.xls!DelHyperLinks
          delShapesOnSht
            --personal.xls!delShapesOnSht
          MakeLastCell
            --personal.xls!makelastcell
     Informative -- Msgbox type stuffn
          FormulaBox
            --personal.xls!FormulaBox
          WhereAmI
            --personal.xls!WhereAmI
     Listings -- Destructive/Documentation
          &Custom Menu Item
          BuildTOC_A3
            --personal.xls!BuildTOC_A3
          Command Bar List (CB_List)
          FormulaIntoComments
            --personal.xls!FormulasIntoComments
          Get Shaps Proc
            --personal.xls!getShapeProc
          List Functions and Subs
            --personal.xls!ListFunctionsAndSubs
          EnumerateAddins
            --personal.xls!EnumerateAddIns
          Enumerate Sheets_95
            --personal.xls!EnumerateSheets_XL95
          ReOrderSheets -- Reorders Sheet Tabs
            --personal.xls!ReOrderSheets
     Mark Cells
       --personal.xls!MarkCells
     Mark Sep Areas
       --personal.xls!MarkSepAreas
     Wordpad when on laptop
       --personal.xls!A_Selected_Delete_Rows
     Create Simulated Data
          Fill Sequence
            --personal.xls!FillSequence
          Mark Sequence
            --personal.xls!MarkSeq
          Random003
            --personal.xls!Random003  
          Random A-Z 
            --personal.xls!RandomAZ 
     GoTo Cell, HTML, Hyperlink, Sheet, Sub
          Go To Cell
            --personal.xls!GoToCell
          Go To HTML
            --personal.xls!GoToHTML
          Go To Hyperlink
            --personal.xls!GoToHyperLink
          Go To Sheet
            --personal.xls!GoToSheet
          Go To Sub
            --personal.xls!GoToSub
          Sort Worksheet Tabs
            --personal.xls!SortALLSheets
     Reformat:  Join,
          HTML - xl2HTMLx
            --personal.xls!XL2HTMLx
          HTML - xl2HTML
            --personal.xls!XL2HTML
          HTML - xl2HTMLs - Miultile Sheets
            --personal.xls!xl2HTMLs
          Clear Clipboard
            --personal.xls!Clear_Clipboard
          Fix US Zip 5
            --personal.xls!fixUSzip5
          Fill Sequence
            --personal.xls!fillsequence
          Firstname
            --personal.xls!FirstName
          InsertCellReplacement
            --personal.xls!InsertCellReplacement
          Insert Prefix**
            --personal.xls!insertprefix
          Join
            --personal.xls!Join
          Lastname
            --personal.xls!LastName
          Lower_Case
            --personal.xls!Lower_Case
          MakeHyperlinks
            --personal.xls!MakeHyperlinks
          MakeHyperlinkFormulas
            --personal.xls!MakeHyperlinkFormulas
          Make INDIRECT
            --personal.xls!Make_INDIRECT
          Proper_Case
            --personal.xls!Proper_Case
          Remove Extra Lines Within Cells
            --personal.xls!Remove_XtraLinesWithinCells
          Reversi
            --personal.xls!ReversI
          RotateCW (top to bottom)
            --personal.xls!RotateCW
          Sep Term
            --personal.xls!SepTerm
          Sep Last Term
            --personal.xls!SepLastTerm
          Suffix (and Prefix)
            --personal.xls!Suffix 
          TrimALL
            --personal.xls!TrimALL
          Upper_Case
            --personal.xls!Upper_Case
     Selections  Hyperlinks, Sheets
          Random003
            --personal.xls!Random003 
          IconExplorer.exe -- get icons for toolbars
            --personal.xls!IconExplorer
          Select Hyperlinks
            --personal.xls!SelectHyperlinks
          selShapesOnSht (selective delete)
            --personal.xls!selShapesOnSht

Locate a specific Subroutine with Google Web Search (#locate)

If you want to find the code associated with any of the above you can use a Google web search as follows:
  site:www.mvps.org  inurl:dmcritchie  inurl:excel  Sub*join

Prepare the above data for input into MenuMaker (#prepare)

The above data can be prepared for use with the CreateMenu macro in MenuMaker by copying into B2 of menu maker and place 12 or whatever the number for the menu would be on the level 1 entry.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim r   As Long, lvl As Long, newstr As String
   For r = ActiveCell.Row To 2 Step -1
     If Left(Cells(r, 2), 1) = " " Then
       Cells(r, 1) = (Len(Cells(r, 2)) - Len(LTrim(Cells(r, 2)))) / 5 + 1
       If Left(LTrim(Cells(r, 2)), 2) = "--" Then
          Cells(r - 1, 3) = Mid(Trim(Cells(r, 2)), 3)
          Cells(r, 2).EntireRow.Delete
       Else
          Cells(r, 2) = Trim(Cells(r, 2))
          If Cells(r, 2) = "" Then Cells(r, 2).EntireRow.Delete
       End If
     End If
   Next r
End Sub

Updates (#updates)

2006-05-12 fixed messed up duplicate pesonal.xls
Expect problem was caused by %$%#^% Excel 2002 recovery process and pointing to older version of Excel.  The toolbars referenced a different pesonal.xls normally would run Bill Manville's fixup (see related), but moved the older copies out of the other XLSTART of Excel 2000 into completely different folder, restarting Excel no longer had toolbars pointing to those older files -- no need to run fixup, but Excel 2002 was opening (for past several days) the pesonal.xls files from two different versions really messing up things.  Eliminated the personal.xls of which only the copy in the old Excel 2000 was still around, and the files within the toolbars fixed themselves.  Fed up with Windows files, lack of a proper catalog and security, Windows updates, Excel 2002 recovery, lack of support for multiple versions at same time.

This page was introduced on August 04, 2002. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved