Copying a Command BarThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
You must use Microsoft® Visual Basic® for Applications (VBA) code to copy an existing command bar. You create a copy of a command bar by creating a new command bar of the same type as the one you want to copy, and then use the CommandBarControl object's Copy method to copy each control from the original command bar to the new command bar. The following procedure illustrates how to use VBA to copy an existing command bar:
Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup Else cbrCopy.Visible = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function
This procedure will not work if you pass in the name of an existing command bar in the strNewCBName argument, because that argument represents the name of the new command bar.
Note If you copy a pop-up menu and set the blnShowBar argument to True, the pop-up menu will be displayed at the current location of the mouse pointer. For more information about displaying pop-up menus, search the Microsoft Office Visual Basic Reference Help index for "ShowPopup method."
Working with Command Bars | Manipulating Command Bars and Command Bar Controls with VBA Code | Getting Information About Command Bars and Controls | Creating a Command Bar | Hiding and Showing a Command Bar | Deleting a Command Bar | Preventing Users from Modifying Custom Command Bars | Working with Personalized Menus | Working with Images on Command Bar Buttons | Working with Command Bar Controls | Working with Command Bar Events