Excel cell name find

Question

So I am trying to create a function, where I use a dropdown to change the language of the excel sheet displayed. My initial idea was to give cells a name and then create function which looks up the cell name in a table and then, depending on the language, an offset is applied, so that it then changes the language in a specific cell.

Private Sub Worksheet_Change(ByVal Target As Range)
'///////////////INITIATES THE LANGUAGE CHANGE AND RETURNS OFFSET
Dim strLng As String
strLng = Sheet9.Range("A1").Value

On Error Resume Next

    If Target.Address = "$A$1" Then
        Select Case strLng
            Case "DA"
               LanguageChange (1)
            Case "EN"
                LanguageChange (2)
            Case "DE"
                LanguageChange (3)
        End Select
    End If
End Sub

I then was thinking to use a table like below and then use below function in a module to find the text id and use the offset to apply the new word. The problem however is, that nothing is happening, when I do that and I cannot find the error. Any suggestions or ideas :)

[

Option Explicit


'///////////////////////////////////////////////////CHANGE LANGUAGE
Public Function LanguageChange(LngOffset As Integer)

Dim LCcell As Name
Dim LCrng As Range

On Error Resume Next
Application.ScreenUpdating = False
    For Each LCcell In Sheet9.Names
        Set LCrng = Sheet3.Range("AA1:AA250").Find(LCcell, LookIn:=xlValues, MatchCase:=False).Offset(0, LngOffset)
        LCcell.Value = LCrng.Value
    Next
Application.ScreenUpdating = True

End Function

Answer 1

A Worksheet Change: Change Values (Language) in Named Cells

Sheet Module Sheet9

Private Sub Worksheet_Change(ByVal Target As Range) ' in 'Sheet9'
    
    Dim tcell As Range: Set tcell = Me.Range("A1")
    If Intersect(tcell, Target) Is Nothing Then Exit Sub ' not drop down
    
    Dim Languages As Variant: Languages = Array("DA", "EN", "DE")
    
    Dim ColumnIndex As Variant:
    ColumnIndex = Application.Match(CStr(tcell.Value), Languages, 0)
    
    If IsNumeric(ColumnIndex) Then ChangeLanguage ColumnIndex
    
End Sub

Private Sub ChangeLanguage(ByVal ColumnIndex As Long)

    Dim nrg As Range: Set nrg = Sheet3.Range("AA2:AA250")

    Application.EnableEvents = False ' prevent retriggering 'Worksheet Change'
    
    Dim nm As Name, RowIndex As Variant
    
    For Each nm In Me.Parent.Names ' workbook scope
    'or:
    'For Each nm In Me.Names ' if worksheet scope!
        RowIndex = Application.Match(nm.Name, nrg, 0)
        If IsNumeric(RowIndex) Then ' name found in list
            With nm.RefersToRange
                If .Cells.CountLarge = 1 Then ' name has one cell
                    .Value = nrg.Cells(RowIndex).Offset(, ColumnIndex).Value
                'Else ' name has multiple cells; do nothing
                End If
            End With
        'Else ' name not found in list; do nothing
        End If
    Next nm

    Application.EnableEvents = True

End Sub