Consulting

Results 1 to 8 of 8

Thread: Multilingual Ribbon UI

  1. #1

    Multilingual Ribbon UI

    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:

    [VBA]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[/VBA]

    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?)

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    this would be the basic approach I'd start with

    [VBA]
    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
    [/VBA]

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

    Paul

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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.

    [VBA]
    Option Explicit
    'See http://msdn.microsoft.com/en-us/libr...(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

    [/VBA]

    Paul

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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

  6. #6
    Thanks to you also, xld. I certainly hadn't given Access a thought. Great point to make.

    Roderick

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Aah, but I always provide an installer Paul, that does the lifting for them.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •