PDA

View Full Version : Multilingual Ribbon UI



Roderick
05-04-2013, 12:18 PM
Does anyone have any ideas on creating a multilingual Ribbon Bar tab for a Word template, please? Allow me to explain . . . I have created four templates for a client (a Letterhead, in fact) in Word 2010 and now they have asked me if I can create just one template but allowing the user to change the language of the control labels on the additional Ribbon tab which loads when the template is run. This also includes the keytips as well as the supertips text.

Doing some research I found the ideal thing in the RibbonX book (Martin, Puls & Hennig). It works beautifully but only in Excel! The key to the whole thing is a series of LOOKUP tables containing the text for the labels, etc.

A code snippet is below to demonstrate the structure in the Excel VBA procedures:

Option Explicit
Public grxIRibbonUI As IRibbonUI
Public giColControls As Integer
Public gWS As Worksheet

Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
Set grxIRibbonUI = ribbon
giColControls = 2
Set gWS = ThisWorkbook.Sheets("Languages")
Application.SendKeys "%UN{RETURN}"
End Sub

Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)
On Error Resume Next
returnedVal = Application.WorksheetFunction.VLookup(control.ID, gWS.Range("A2:C200"), giColControls, 0)
End Sub

Is there any way I can access an external Excel worksheet (without opening Excel) and then apply a LOOKUP procedure to the sheet containing the lookup tables through Word VBA? Or can anyone suggest an alternative method to achieve the same result?

Thanks for any help. (btw, should this code question be in the VBA forum or is it OK here?)

Paul_Hossler
05-05-2013, 01:39 PM
this would be the basic approach I'd start with


Option Explicit
Public grxIRibbonUI As IRibbonUI
Public giLanguage As Long


Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
Set grxIRibbonUI = ribbon

'doesn't have to be here
giLanguage = 1

End Sub

Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)

Select Case control.ID
Case "bOne"
Select Case giLanguage
Case 1
returnedVal = "Lable text 1 in Language 1"
Case 2
returnedVal = "Lable text 1 in Language 2"
Case 3
returnedVal = "Lable text 1 in Language 3"
Case 4
returnedVal = "Lable text 1 in Language 4"
End Select

Case "bTwo"
Select Case giLanguage
Case 1
returnedVal = "Lable text 2 in Language 1"
Case 2
returnedVal = "Lable text 2 in Language 2"
Case 3
returnedVal = "Lable text 3 in Language 3"
Case 4
returnedVal = "Lable text 4 in Language 4"
End Select
End Select
End Sub


For a production use, I'd probably use collections or arrays to hold the strings and init that when the OnLoad calback fired

Paul

Paul_Hossler
05-05-2013, 01:48 PM
Also, if you want to read the Control Panel Windows Regional Settings to automatically figure out where the PC is and automatically switch to the right language, this will return USA, ENG, FRA, etc.


Option Explicit
'See http://msdn.microsoft.com/en-us/library/dd373739(v=VS.85).aspx for meaning of various constants.
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long
Private Const LOCALE_SABBREVCTRYNAME As Long = &H7
Private Const LOCALE_USER_DEFAULT As Long = &H400

Sub ShowWindowRegionalSettingsCountry()
MsgBox CountryNameShort
End Sub

Property Get CountryNameShort() As String 'USA
CountryNameShort = pvtGetInfo(LOCALE_SABBREVCTRYNAME)
End Property

Function pvtGetInfo(ByVal lInfo As Long) As String
Dim Buffer As String
Dim ret As String
Buffer = String$(256, 0)
ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If ret > 0 Then
pvtGetInfo = Left$(Buffer, ret - 1)
Else
pvtGetInfo = vbNullString
End If
lbl_Exit:
Exit Function
End Function



Paul

Bob Phillips
05-06-2013, 03:14 AM
You could also use ini files and use GetPrivateProfileString and the like to read them.

Or better still, drop them into an Access table and read from there. Your client doesn't need Access itself to use Access databases.

Roderick
05-06-2013, 07:50 AM
Thanks Paul. I'm now going to go away and puzzle over this for a few days and see if I can get close to a solution. Forgive me if I have to come back with some additional questions.

Roderick

Roderick
05-06-2013, 07:53 AM
Thanks to you also, xld. I certainly hadn't given Access a thought. Great point to make.

Roderick

Paul_Hossler
05-06-2013, 10:28 AM
Roderick --



You could also use ini files and use GetPrivateProfileString and the like to read them.
Or better still, drop them into an Access table and read from there. Your client doesn't need Access itself to use Access databases.


XLD certainly has the more elegant solution if the additional files can be maintained and distributed to the users who will need them.

The 'less than sophisicated' users that I work with have a hard time putting the .dotm in the right folder, so my first thought is usually to try and keep things as self contained as possible.

Paul

Bob Phillips
05-08-2013, 08:44 AM
Aah, but I always provide an installer Paul, that does the lifting for them.