Excel

Create menu for use in international environment distributed as xla

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

tpoynton

Description:

The code provided here will allow you to create custom menu items that work in an international environment. 

Discussion:

A popular way to create custom menu items in VBA is documented in this KB entry (http://vbaexpress.com/kb/getarticle.php?kb_id=427). However, in international environments, using "Worksheet Menu Bar" and "Help" (for example) as references does not always work, because "Worksheet Menu Bar" and/or "Help" may be called something else in other language versions of Excel. A simple alternative is to use the control ID #'s instead of the string names. A list of the control ID's can be found on Microsoft's website at http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q213552 . The code below is geared towards developing a menu-driven Excel addin that is distributed as an xla file. Where code needs to be inserted in the VBE and the steps needed to create a redisributable xla are provided in comments embedded in the code below. 

Code:

instructions for use

			

Option Explicit 'This section of code goes in ThisWorkbook. When the addin is 'installed, it runs the procedure to add the desired menu and 'menu items to the toolbar. This example adds a new menu before the 'Excel Help menu in the worksheet menu bar. To see how to install 'the addin when it is complete, see comments at the bottom of this code 'For a list of control ID's, visit 'http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q213552 Private Sub Workbook_AddinInstall() Dim ctrlMain As CommandBarPopup 'this is what is seen next to "Help" Dim ctrlItem As CommandBarControl 'this is what is seen when the new menu is clicked on Dim iHelpIndex As Long 'this will be set to the position of "Help" Dim cbHelp As CommandBarControl 'used to find existing "Help" item 'This finds the position of the Help menu; returns an integer Set cbHelp = Application.CommandBars(1).FindControl(ID:=30010) iHelpIndex = cbHelp.Index 'each time Excel is started, we need to remove and rebuild the 'menu so that the user doesnt end up with a bunch of them. KillMenu 'this next section builds the menu itself in front of the Help menu Set ctrlMain = Application.CommandBars(1).Controls.Add _ (Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False) With ctrlMain 'having the "&" allows alt key combos to work .Caption = "&My New Menu" 'for each desired menu item, add a control. There are two here. Set ctrlItem = .Controls.Add(Type:=msoControlButton) With ctrlItem .Caption = "&Item #1" 'the OnAction command defines the sub in this workbook to run .OnAction = "ThisWorkbook.doItem1" End With Set ctrlItem = _ .Controls.Add(Type:=msoControlButton) With ctrlItem .Caption = "&Item #2" .OnAction = "ThisWorkbook.doItem2" End With End With End Sub 'We also want to make sure our menu disappears when the user 'uninstalls the addin from the addins dialog box Private Sub Workbook_AddinUninstall() 'this removes the menu itself KillMenu 'this makes sure that the addin is uninstalled. Dim xlaName As String Dim i As Integer 'this loops through all addins and removes this one With ThisWorkbook For i = 1 To AddIns.Count xlaName = AddIns(i).Name If xlaName = "sampleMenu.xla" Then AddIns(i).Installed = False End If Next i End With End Sub 'this removes the menu from the menu bar Sub KillMenu() Dim cmdBar As CommandBar Set cmdBar = Application.CommandBars(1) 'this turns off error checking and deletes the menu item On Error Resume Next Set cmdBar = Application.CommandBars(1) cmdBar.Controls("&My New Menu").Delete On Error GoTo 0 'turns error checking back on End Sub 'each sub here runs when the user clicks on the appropriate menu item Sub doItem1() 'here you can call dialog boxes or insert code for the action to 'take when the user clicks on the menu item 'Here we will just show a message box MsgBox ("Thanks for clicking on Item #1!") End Sub Sub doItem2() MsgBox ("Thanks for clicking on Item #2!") End Sub 'NOW that you have all of this code in ThisWorkbook, it is time to 'save it as an xla. To do this, go to the Excel menu (not the VBE menu) 'and select File > Save As. In the Save As dialog box, you should find '"Microsoft Office Excel Add In (*.xla)" at the very bottom. Give your 'xla a name, note the location and click Save. NOW, go to the tools 'menu in Excel, select "Add Ins" from the tools menu, click on 'Browse (Select on Mac), and navigate to the location where you saved 'the xla file. Highlight the xla, click OK twice, and you should see 'the new menu appear!

How to use:

  1. open the VBE by pressing alt+F11
  2. insert the code into ThisWorkbook
  3. modify code as desired
  4. save file as type xla in Excel, not VBE
  5. go to Tools>Add Ins in Excel
  6. click Browse (or Select on Mac) to navigate to location where you saved the xla
  7. highlight xla file name and click OK twice
  8. your new menu should appear
 

Test the code:

  1. save the sample xla file to your computer
  2. go to Tools>Add Ins in Excel
  3. click Browse (or Select on Mac) to navigate to location where you saved sampleMenu.xla
  4. highlight sampleMenu.xla file and click OK twice
  5. New menu named "My New Menu" should appear next to Help menu
 

Sample File:

sampleMenu.zip 10.55KB 

Approved by mdmackillop


This entry has been viewed 283 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express