providing reports in multiple languages

**moschen** · 08-13-2009, 12:00 PM

Hello,

we have a couple of reports that we would like to make "on-the-fly-translateable". I mean that the user should be able to select a language from a drop-down box. Once the user changed the language, all lables and comments (which provide descriptions for the lables) should be exchanged to show the version associated with the new selected language. The different languages for the lables and comments should be easily maintainable by the users.

How would you design the solution? I have already some ideas, but as I am quite new to Excel programming I thought it would be a good idea to get some input from more experienced users first.

Sorry for my bad english.

Best regards,
Mo.

**Leith Ross** · 08-13-2009, 12:49 PM

Hello Mo,

The current support for multilingual interfacing is fractured. Not all of the Windows operating system is effected by available upgrades nor are all languages supported. There is also the matter of licensing to consider when upgrading. If you can, you may want to wait until the release of Windows 7. This is supposed to have full MUI (Multilingual User Interfacing) built in.

**JBeaucaire** · 08-13-2009, 12:51 PM

Don't actually need programming, you just need a LANGUAGE sheet. In the sheet down column A are all your English titles and phrases to be used in the book. Build this list as you build your sheet.

Columns B, C etc have your other languages.

Somewhere in your sheet you need a "Select Language" cell, the drop down box you mentioned. Based on that cell's answer, you fill in your labels all throughout your sheet. You can NAME that cell if you wish to make it easy to refer to in formulas, name is Lang if you wish.

Then in your main sheet, instead of just putting "TOTAL" in a cell, you can put:
=IF(LANG="English", Language!A3, Language!B3)

Or, with more than two languages, perhaps:
=LOOKUP(LANG, Language!$A$1:$D$1, Language!$A$3:$D$3)

This get you started?

**Leith Ross** · 08-13-2009, 01:22 PM

Hello Mo,

The translation capabilities of the language interface are limited to thefollowing. Also you must have an English version of Office to make use of the MUI. If you need to translate words or phrases other than those listed, Office can't do it.

Microsoft Office 2003 Multilingual User Interface Pack adds key multilingual capabilities to those already built into Microsoft Office by providing the translated text for the user interface, Help, wizards, and templates for Office programs. By using Office 2003 MUI Pack, you're working in the English version of Office, but you can view commands, dialog box options, Help topics, wizards, and templates in a familiar language.

**moschen** · 08-14-2009, 03:01 AM

Thanks JBeaucaire!

Do you also have an idea on how to translate the comments. I think I have to use VBA for it. An additional difficulty is, that not all lables have a comment.

My curent idea is to use two columns per language. One contains the lable text and the second one an optional comment text. Then I could get all formulars of a sheet. If the formular is referencing the "language sheet" (e.g. the forumlar starts with "=LOOKUP(LANG, Language") ), then l can lookup if there is a comment text associated with the lable text. If so, put the comment, otherwise delete the comment, if there is one.

What do you think?

Best regards,
Mo.