If you create an add-in or workbook that will be used by people that use different language versions of Excel it is nice if you can change the menu labels/description on the Ribbon to local words.
Open the example file "GetLabel.xlsm"
You see a custom group with 3 buttons on a new Tab named “My Tab”
Use the Custom UI Editor to see the RibbonX that create this custom group on the new tab.
We use a table on a worksheet with all controls ID's and the label names in every language you want.
When we open the workbook we use this macro to find the country number and set the variable ColNum to the correct column with the correct language.
Sub Auto_Open() Select Case Application.International(xlCountryCode) Case 31: ColNum = 2 'Dutch Case Else: ColNum = 3 'English End Select End Sub
In the RibbonX of the workbook we use getLabel to run the callback
RDB_getlabel to give the Group and every Button a name.
In this callback we use the Vlookup function to find the control ID in the table and display the value in column number ColNum.
For a list of country codes see: http://support.microsoft.com/kb/213833/en-us
Example 1: Open the example file "DynamicMenu-1.xlsm"
You see a custom group "My Menu" on the Home tab with a Dynamic menu
Use the Custom UI Editor to see the RibbonX that create this custom group on the Home tab.
In the RibbonX of the workbook we add a Dynamic menu like this.
<dynamicMenu id="RDBDynamicMenu" getContent="RDBdynamicMenuContent"
imageMso="HappyFace" label="My Dynamic Menu"/>
When we open the workbook we check the language version of Excel and we use getContent to run the callback RDBdynamicMenuContent that load the correct menu items. (English, Dutch or German in this example)
Example 2 : Open the example file "DynamicMenu-2.xlsm"
In the first workbook we check the language version of Excel and use the
RDBdynamicMenuContent callback to load the correct menu (English, Dutch
or German in this example)
In this workbook there is a dropdown to select the language(default is English). Note: There is more code in this workbook because we must reload the RibbonX each time you change the language in the dropdown. Compare the VBA and RibbonX in this workbook with the VBA and RibbonX in the workbook DynamicMenu-1.xlsm to see the changes.