Excel changing multiple cell details based on a specific cells value-CodePudding

Super new to Excel formulas and vba. Any insight you can provide is appreciated.

I am trying to implement a language switcher in my spreadsheet. I currently have the following worksheets: Data (place to enter values in columns), Summary (summary of everything entered in Data worksheet), F (place to store functions and drop down list items), T (english / french language translations)

There are cells in all worksheets that will be translated when either English or French language is chosen. In the T worksheet there are 3 columns where A is a reference column, B has English words, C has the translated French words.

The drop down that switches languages is in the Data worksheet. I have entered the following code in the visual basic editor (after double clicking on the Data worksheet from the list).

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = Range("C5")
Dim langCol

If Target.Value = "English" Then
    langCol = 2 'now it's in english
Else
    langCol = 3 'now it's in french

End If

Sheets("Data").Cells(1, 1).Value = Sheets("T").Cells(5, langCol).Value
Sheets("Data").Cells(2, 1).Value = Sheets("T").Cells(6, langCol).Value
'....many more cell swaps here 100 total
End Sub

This code works when I run it as a 'worksheet selection change' and translates everything as expected when I choose either English or Français from the drop down located at 'C5'. My problem is this code only executes after I click away from the 'C5' cell. The desired outcome is having the translation happen as the language is chosen, while the 'C5' cell is still active.

When I swap the opening line of code with:

Private Sub Worksheet_Change(ByVal Target As Range)

the code works ONLY when there is a single cell swap before the 'end sub'. As soon as an additional cell swap is added, Excel (2007) crashes and restarts.

Is there something I am missing in the code above that would obviously cause an error? I am not getting any feedback from the program itself.

I have considered that making 100 cell swaps may be the problem. Is there a smarter way to handle that with wildcards or a more elegant approach?

CodePudding user response：

A Drop-Down Worksheet Change

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ClearError ' mainly to prevent exiting without enabling events
     
    ' Target could be multiple cells. Restrict to one cell.
    Dim tCell As Range: Set tCell = Intersect(Range("C5"), Target)
    If tCell Is Nothing Then Exit Sub
    
    Dim langCol As Long
    
    Select Case tCell.Value
        Case "English": langCol = 2
        Case "French": langCol = 3
        ' easily add more
        Case Else: Exit Sub
    End Select
    
    Application.EnableEvents = False ' prevent retriggering the event
    
    ' Sheets("Data") could be replaced with 'Me' and use a variable for others.
    ' To improve readability you could do:

    Dim tws As Worksheet: Set tws = Me.Parent.Worksheets("T")

    Cells(1, 1).Value = tws.Cells(5, langCol).Value
    Cells(2, 1).Value = tws.Cells(6, langCol).Value
    '....many more cell swaps here 100 total

SafeExit:
    If Not Application.EnableEvents Then Application.EnableEvents = True
    
    Exit Sub

ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub