Create multilingual userform

If an application is used by users with different languages, it is nice for the user experience that each user sees the form in his or her own language.

When starting a form, the language of the application must first be checked, after which the form is automatically displayed in the language of the user. If a translation is not available, the default language is shown. After starting up the form, the user has the option to change the language.

To make a form multilingual, a translation table on a separate sheet must first be added to the file. In the VBA editor, call this sheet shTranslations.

Each translation must be given a unique ID.

The language code of the relevant translation must be entered in the column header. This can then be compared with the current language of the application.

Language codes can be looked up on language codes. Different language codes for the same language can be put in the same column separated by an underscrore, The language of the application can be read with: Application.LanguageSettings.LanguageID(msoLanguageIDUI)

Next, on the form, for each control to be translated, the ID of the relevant translation must be placed in the Tag property of the control.

When starting the form, these tags are first automatically read and then the associated controls are provided with the correct translation.

The following code can be used to translate these controls:

'------------------------------------------------------------------------------------------------------------------------------
' Author: Manfred van den Noort
' Copyright : © 2021 worksheetsvba.com, all rights reserved
' Version    : 1.0
' Date      : 2022-01-03
' Purpose   : MultiLingual Form
'------------------------------------------------------------------------------------------------------------------------------

Public Sub TranslateForm(oForm As Object, lLangCode As Long)
    Dim ctl As Control, lLangColumnNumber As Long, sTranslation As String, i As Long, arr
    lLangColumnNumber = GetColumnNumberOfLanguageCode(lLangCode)
    If IsNumeric(oForm.Tag) Then
        sTranslation = GetTranslation(lLangColumnNumber, oForm.Tag)
        If sTranslation <> vbNullString Then
            oForm.Caption = sTranslation
        End If
    End If
    For Each ctl In oForm.Controls
        Select Case TypeName(ctl)
            Case "CommandButton", "Label", "Frame", "CheckBox", "OptionButton", "ToggleButton"
                If IsNumeric(ctl.Tag) Then
                    sTranslation = GetTranslation(lLangColumnNumber, ctl.Tag)
                    If sTranslation <> vbNullString Then
                        ctl.Caption = sTranslation
                    End If
                End If
            Case "TabStrip"
                If ctl.Tag <> vbNullString Then
                    arr = Split(ctl.Tag, "_")
                    For i = 0 To ctl.Tabs.Count - 1
                        If i <= UBound(arr) Then
                            If IsNumeric(arr(i)) Then
                                sTranslation = GetTranslation(lLangColumnNumber, CLng(arr(i)))
                                If sTranslation <> vbNullString Then
                                    ctl.Tabs(i).Caption = sTranslation
                                End If
                            End If
                        End If
                    Next
                End If
            Case "MultiPage"
                For i = 0 To ctl.Pages.Count - 1
                    With ctl.Pages(i)
                        If IsNumeric(.Tag) Then
                            sTranslation = GetTranslation(lLangColumnNumber, .Tag)
                            If sTranslation <> vbNullString Then
                                .Caption = sTranslation
                            End If
                        End If
                    End With
                Next
        End Select
    Next
End Sub

Public Function GetColumnNumberOfLanguageCode(lLangCode As Long) As Long
    Dim lColNo As Long, i As Long
    lColNo = 2 'default
    For i = 2 To shTranslations.UsedRange.Columns.Count
        If InStr(shTranslations.Cells(1, i).Value, "_" & lLangCode & "_") Then
            lColNo = i
        End If
    Next
    GetColumnNumberOfLanguageCode = lColNo
End Function

Private Function GetTranslation(lLangColumnNumber As Long, iTranslationID As Integer) As String
    Dim iTranslationRow As Long
    iTranslationRow = Evaluate("iferror(match(" & iTranslationID & ",'" & shTranslations.Name & "'!A:A,0)," & 0 & ")")
    If iTranslationRow > 0 Then
        GetTranslation = shTranslations.Cells(iTranslationRow, lLangColumnNumber).Value
    End If
End Function

Then radio buttons must be added to the form for each language and when the form is started, the language of the application is read.

This code translates most common controls. As mentioned, the translation ID must be stored in the Tag property of the control. If this Tag is currently also being used for something else, the code must be adapted for this. Adjustments are also necessary if the ControlTipText of controls and the Accelerator of a command button are also to be translated/translated. For example, this can be done by putting multiple translation IDs in a Tag, which are separated by a pipe.

An example file can be downloaded below showing the working of a multilingual form. This file contains some English and Dutch sample translations. If a user's language is not found, the English translation is shown by default. Hopefully this file can help you create your own multilingual form.

Multilingual form 1.0

Download

Questions / suggestions

Hopefully, this article helped you creating a multilingual userform. If you have any questions about this topic or suggestions for improvement, please post a comment below.