Solved: Distribute xla in other language versions of Excel

Looks like i am having a conversation with myself...that's OK!

I was testing out the possibility of autodetecting the language...a colleague in Mexico appears to have an English installation! The problem with asking the user to select a langauge is that it has to be done every time excel is launched...I know i could write a registry value or something like that to have it "remember" the setting, but i would like to avoid doing that...

this just gave me a thought; i could write the value to a worksheet within the xla...

ANYWAY, i put together all of the language codes in the attached sheet, and three different ways of obtaining them via VBA. Right now when you open the file, it will tell you the results of the three methods (IDUI, IDInstall, and IDHelp). I found it helpful, so I thought I would share...

Quote:

---

Originally Posted by tpoynton

Looks like i am having a conversation with myself...

---

...you're not alone, honest! :grouphug:

Thanks for documenting your progress on the board :friends: the thread's getting a lot of views and I've no doubt it will be a useful reference for the future. (Not least for me, that's a long list of language ID's I know I won't ever have to look up myself) :thumb

hi there...

please let me throw in my 5 euro cent...

some time back in 2001 i had the idea to overcome the limits of excel's autofilter as far as its lookup criteria is concerned and a very friendly guy named george marcus coded the whole thing for me...

i then used original excel-97 on german win-98 and never had any problems with his addin...

now i'm still using it and moved it to excel 2003 on original xp-pro...still no problems...

i gave the addin to a friend who runs xp on the german version of xp-home and guess what...she could load the addin allright, but it did not appear on the tool bar...although the addin code could be read in the vba-environment...

now what....i asked the famous andrew engwirda for help and he came up with the following correction:

' HelpIndex = CommandBars(1).Controls("Help").Index = original code
HelpIndex = Application.CommandBars.FindControl(ID:=30010).Index = his code

and voila, it appeared by magic...

maybe you can use his correction as well if you transport some stuff from the us-version to the german environment...

keep up the good work and have a nice day...

best,
wolfgang

OK - want to mark the thread as solved, and provide (yet) another update. Here's what I've come up with to prepare distributing an xla in multiple languages:

1 - ensure commandbar controls are referenced by their ID instead of their String name

2 - if possible, plan at the outset for standardizing userform labels and buttons to reduce the amount of translations

3 - in an xla, you can access a worksheet to modify by setting the "addins" property of ThisWorkbook to false. You can use this worksheet to store the translations. This is an appealing way to approach this problem because it permits you to still only distribute a single xla file, while you can send a separate xls file to multiple people for translation - then it is a simple matter of copying and pasting from the xls file into the xla file.

Initially, my plan was to autodetect the language settings of the host machine using methods described earlier in this thread. However, after testing with a colleague in Mexico, I found that because he purchased his computer in the US, his Excel version was consistently detected as English (it was, in fact, an English version).

Regardless, I then decided that it would probably be best to have users choose a preferred language. To accomplish this, I am saving the value of a global variable to the worksheet; when the addin is loaded, i set the value of the global variable to the value of the cell in the worksheet. This is simple, but probably worth documenting...

To save the worksheet after the user changes the language setting through a userform, I do this:
[vba]
'get setting from optionbuttons
If OptionButton_Esp.Value = True Then
gLang = 2
ElseIf OptionButton_Ger.Value = True Then
gLang = 3
Else: gLang = 1 '1 is the default, which is English
End If

'This writes the current value of the global variable (gLang) to the
'worksheet in the xla and saves the sheet so that the value is
' "remembered"
ThisWorkbook.Sheets("SheetName").Cells(1, 15).Value = gLang
ThisWorkbook.Sheets("SheetName").Activate
ActiveWorkbook.Save [/vba]

It is also important to note that the value of the global variable should be set to the column of the worksheet that holds the translated text. That way, when you call the code in a userform (or whatever), the location is already set. Below is an example of having the userform elements translated when the userform is opened:
[vba]
Public Sub Userform_Activate()
'if language is not English...
If gLang > 1 Then
With ThisWorkbook.Sheets("SheetName")
Label1.Caption = .Cells(2, gLang).Value
Label2.Caption = .Cells(3, gLang).Value
Label3.Caption = .Cells(4, gLang).Value
Label4.Caption = .Cells(5, gLang).Value
CommandButton1.Caption = .Cells(6, gLang).Value
CommandButton2.Caption = .Cells(7, gLang).Value
End With
end if
[/vba]
SO, the way i have my excel sheet set up is to have English in the first column, Spanish in the second column, etc.

This seems like a pretty simple way to go about doing this...

I appreciate all the help and support I have gotten on this threadhttp://vbaexpress.com/forum/images/smilies/beerchug.gif. I hope other people find this information useful!