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

Home page:
[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 (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
  '--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
      '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
&Custom Button
Toolbar 2
Sort &Ascending
&Custom Button
Toolbar 3
**Insert Row**
**Top of Column**
&Custom Button
Toolbar 4
Toolbar x -- Icon Storehouse
&Custom Button
My Tools saved menu
My Tools
     Char Map
     Deletions -- Destructive
          Convert Hyperlinks
     Informative -- Msgbox type stuffn
     Listings -- Destructive/Documentation
          &Custom Menu Item
          Command Bar List (CB_List)
          Get Shaps Proc
          List Functions and Subs
          Enumerate Sheets_95
          ReOrderSheets -- Reorders Sheet Tabs
     Mark Cells
     Mark Sep Areas
     Wordpad when on laptop
     Create Simulated Data
          Fill Sequence
          Mark Sequence
          Random A-Z 
     GoTo Cell, HTML, Hyperlink, Sheet, Sub
          Go To Cell
          Go To HTML
          Go To Hyperlink
          Go To Sheet
          Go To Sub
          Sort Worksheet Tabs
     Reformat:  Join,
          HTML - xl2HTMLx
          HTML - xl2HTML
          HTML - xl2HTMLs - Miultile Sheets
          Clear Clipboard
          Fix US Zip 5
          Fill Sequence
          Insert Prefix**
          Make INDIRECT
          Remove Extra Lines Within Cells
          RotateCW (top to bottom)
          Sep Term
          Sep Last Term
          Suffix (and Prefix)
     Selections  Hyperlinks, Sheets
          IconExplorer.exe -- get icons for toolbars
          Select Hyperlinks
          selShapesOnSht (selective delete)

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:  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
          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