<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Create Custom Menu Items in Excel VBA

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Custom Menu Items Created on the fly With Excel VBA

Current Special! Complete ExcelExcel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 InstantBuy/Download Includes Advanced Excel and 2 Excel VBA Courses.

Got any Excel Questions? Free Excel Help Adding Custom Menu Item in Excel See
Also: Delete/Restore Excel Toolbars || Adding a Command Button to the Excel Right Click Menu

It is possible to customize Excel in many different ways. However, to make your Excel spreadsheets have that professional look and feel, one can add their own custom menus to an existing Excel toolbar. This is best done via VBA and the same menu items you add, should be deleted upon deactivating/closing the Excel Workbook. Download Working Example

Sample Code to Add/Delete Custom Menu Items

You can simply copy the code below into any standard Excel Module

Sub AddMenus()Dim cMenu1 As CommandBarControlDim cbMainMenuBar As CommandBarDim iHelpMenu As IntegerDim cbcCutomMenu As CommandBarControl'(1)Delete any existing one. We must use On Error Resume next _ in case it does not exist.On Error Resume NextApplication.CommandBars("Worksheet Menu Bar").Controls("&New Menu").DeleteOn Error GoTo 0'(2)Set a CommandBar variable to Worksheet menu bar Set cbMainMenuBar = _     Application.CommandBars("Worksheet Menu Bar")     '(3)Return the Index number of the Help menu. We can then use _ this to place a custom menu before. iHelpMenu = _     cbMainMenuBar.Controls("Help").Index      '(4)Add a Control to the "Worksheet Menu Bar" before Help. 'Set a CommandBarControl variable to it Set cbcCutomMenu = _     cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _                  Before:=iHelpMenu)                       '(5)Give the control a caption     cbcCutomMenu.Caption = "&New Menu"      '(6)Working with our new Control, add a sub control and _ give it a Caption and tell it which macro to run (OnAction). With cbcCutomMenu.Controls.Add(Type:=msoControlButton)                .Caption = "Menu 1"                .OnAction = "MyMacro1" End With '(6a)Add another sub control give it a Caption _  and tell it which macro to run (OnAction) With cbcCutomMenu.Controls.Add(Type:=msoControlButton)                .Caption = "Menu 2"                .OnAction = "MyMacro2" End With  'Repeat step "6a" for each menu item you want to add.     'Add another menu that will lead off to another menu 'Set a CommandBarControl variable to itSet cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)   ' Give the control a caption    cbcCutomMenu.Caption = "Ne&xt Menu" 'Add a contol to the sub menu, just created above With cbcCutomMenu.Controls.Add(Type:=msoControlButton)                .Caption = "&Charts"                .FaceId = 420                .OnAction = "MyMacro2" End WithEnd SubSub DeleteMenu()On Error Resume Next    Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete    On Error GoTo 0End SubSub MyMacro1()MsgBox "I don't do much yet, do I?", vbInformation, "Ozgrid.com"End SubSub MyMacro2()MsgBox "I don't do much yet either, do I?", vbInformation, "Ozgrid.com"End Sub

Code to Fire off the Above Code

The code here must be placed in the Private Module of the Worksheet Object (ThisWorkbook). To get there from within Excel proper, right click on the Excel icon, top left next to "File", and click "View Code". In here paste the code below;

Private Sub Workbook_Activate()Run "AddMenus"End SubPrivate Sub Workbook_Deactivate()Run "DeleteMenu"End Sub

Now save the Workbook and open any other. You will note that as you go from one Workbook to another the custom menu items are deleted. As soon as the Workbook is activated again, the custom menu items are re-created. The same thing happens when closing/opening the Workbook. Download Working Example

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft