Attribute VB_Name = "McRitchie_BarHoppingTheToolbars" Option Explicit Sub barhopper() Attribute barhopper.VB_ProcData.VB_Invoke_Func = "b\n14" 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 (dead link) '--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