Hi RJ,
What i want to do is have a language drop down on the first form, and
if for example, the user selects Spanish, all the labels on that form,
as well as other forms labels should change to Spanish.
I guess we're talking about a userform and not a protected document?
If so, there are different approaches to use:
- Create an ini-file with the different languages as sections and the
labelnames as keys. Use the translation as value.
- If the translations are not really long strings, put them in the tag of
the control on the userform and separate them by a special sign that's not
used in the translation, for example a tilde (~)
Read the translation on the after_update of the combobox with languages or
the change event of a multipage.
For the code I wrote for this example I used an userform with a combox and
two labels. In the tag property of the labels I entered first a text for
Spanish followed by a ~ (the separaton sign) and then the text for English.
Label1 for example has the tag Spanish 1~English 1
Private Sub ComboBox1_AfterUpdate()
Dim oControl As MSForms.Control
If ComboBox1.ListIndex <> -1 Then
For Each oControl In Me.Controls
If TypeOf oControl Is MSForms.Label Then
If Len(oControl.Tag) > 0 Then
oControl.Caption = GetSubString(oControl.Tag, _
ComboBox1.ListIndex + 1, "~")
End If
End If
Next
End If
Set oControl = Nothing
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.Clear
.AddItem "Spanish"
.AddItem "English"
End With
End Sub
Function GetSubString(ByVal sStringIn As String, ByVal iPart As Integer, _
ByVal sSeparationSign As String) As String
Dim iPos As Integer
Dim iLaatstePos As Integer
Dim iLoop As Integer
Dim iPos1 As Integer
iPos = 0
iLaatstePos = 0
iLoop = iPart
Do While iLoop > 0
iLaatstePos = iPos
iPos1 = InStr(iPos + 1, sStringIn, sSeparationSign)
If iPos1 > 0 Then
iPos = iPos1
iLoop = iLoop - 1
Else
iPos = Len(sStringIn) + 1
Exit Do
End If
Loop
If (iPos1 = 0) And (iLoop <> iPart) And (iLoop > 1) Then
GetSubString = ""
Else
GetSubString = Mid(sStringIn, iLaatstePos + 1, iPos - iLaatstePos - 1)
End If
End Function
Hope this helps,
kind regards,
Astrid
