I'm having 3 functions dat make me able to translate in Excel (Translating into English, dutch or French) which work perfect. I'm trying to build now a 4th function that should make the user able to select the language for this translation in a single selection box. The code works until the step where I can select the language, but the selected language doesn't get populated... So actually the last part : Sub TranslateSelect()
Anyone who can help me out with this?
Thanks in advance
Sub French()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Dim book1 As Workbook, book2 As Workbook
Dim Lastrow1 As Long, Lastrow2 As Long
Set book1 = ThisWorkbook
Set book2 = ActiveWorkbook
Set sh1 = book1.Worksheets("Labdata")
Set sh2 = book2.Worksheets("Report")
Lastrow1 = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
Lastrow2 = sh2.Cells(sh1.Rows.Count, "E").End(xlUp).Row
Table2 = sh2.Range("B15:B" & Lastrow2)
Table1 = sh1.Range("A3:E" & Lastrow1)
Dept_Row = sh2.Range("C15").Row
Dept_Clm = sh2.Range("C15").Column
For Each Cl In Table2
On Error Resume Next
sh2.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(Cl, Table1, 5, False)
Dept_Row = Dept_Row + 1
Next Cl
End Sub
'-------------------------------------
Sub English()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Dim book1 As Workbook, book2 As Workbook
Dim Lastrow1 As Long, Lastrow2 As Long
Set book1 = ThisWorkbook
Set book2 = ActiveWorkbook
Set sh1 = book1.Worksheets("Labdata")
Set sh2 = book2.Worksheets("Report")
Lastrow1 = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
Lastrow2 = sh2.Cells(sh1.Rows.Count, "C").End(xlUp).Row
Table2 = sh2.Range("B15:B" & Lastrow2)
Table1 = sh1.Range("A3:C" & Lastrow1)
Dept_Row = sh2.Range("C15").Row
Dept_Clm = sh2.Range("C15").Column
For Each Cl In Table2
On Error Resume Next
sh2.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(Cl, Table1, 3, False)
Dept_Row = Dept_Row + 1
Next Cl
End Sub
'-------------------------------------
Sub Dutch()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Dim book1 As Workbook, book2 As Workbook
Dim Lastrow1 As Long, Lastrow2 As Long
Set book1 = ThisWorkbook
Set book2 = ActiveWorkbook
Set sh1 = book1.Worksheets("Labdata")
Set sh2 = book2.Worksheets("Report")
Lastrow1 = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
Lastrow2 = sh2.Cells(sh1.Rows.Count, "D").End(xlUp).Row
Table2 = sh2.Range("B15:B" & Lastrow2)
Table1 = sh1.Range("A3:D" & Lastrow1)
Dept_Row = sh2.Range("C15").Row
Dept_Clm = sh2.Range("C15").Column
For Each Cl In Table2
On Error Resume Next
sh2.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(Cl, Table1, 4, False)
Dept_Row = Dept_Row + 1
Next Cl
End Sub
'-------------------------------------
Sub TranslateSelect()
Dim strLanguage As String
Dim varArrayList As Variant
varArrayList = Array("ENGLISH", "DUTCH", "FRENCH")
strLanguage = SelectionBoxSingle(List:=varArrayList, Prompt:="Select a Language to translate to", Title:="Select Language")
If strLanguage = "ENGLISH" Then Call English
If strLanguage = "FRENCH" Then Call French
If strLanguage = "DUTCH" Then Call Dutch
End Sub