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