Im in a workbook with 2 pages (one called character sheet and another called combat). I have a dropdown menu pulling information from the character sheet and putting the value in cell E2. Cell E2 have been merged and centered from E2:G2. I’m trying to run a script that will see that the dropdown menu has changed and paste a bunch of info below it.

This is the script that I’m trying to use to call the sub

Private Sub Worksheet_Change(ByVal Target as Range)
    
    Dim selectedCell As Range

    ' Store the currently selected cell
    Set selectedCell = Selection

        Application.EnableEvents = False
        Call Weapons
        Application.EnableEvents = True

    ' Reselect the previously selected cell
    selectedCell.Select
    
End Sub

The sub I am calling is

Sub Weapons()
    
    ' Define worksheets so thing can get cross referential
    Dim CS As Worksheet
    Set CS = ThisWorkbook.Sheets("Character Sheet")
    
    Dim combat As Worksheet
    Set combat = ThisWorkbook.Sheets("Combat")
    
    ' Make a variable for to hit
    Dim TH As Integer
    
    ' Greatsword
    If combat.Range("E2") = "Greatsword" Then
        
        TH = CS.Range("E1") + CS.Range("C8")
        
        With combat
            .Range("E4").value = "2d6"
            .Range("F4").value = CS.Range("E1")
            .Range("G4").value = "Slashing"
            .Range("F3").value = TH
            .Range("G3").value = "To hit"
        End With
    
    ' Eldritch Blast
    ElseIf combat.Range("E2") = "Eldritch Blast" Then
    
        TH = CS.Range("C8") + CS.Range("E6")
        
        With combat
            .Range("E4").value = "2d10"
            .Range("F4").value = CS.Range("E6")
            .Range("G4").value = "Force"
            .Range("F3").value = TH
            .Range("G3").value = "To hit"
        End With
    
    End If
    
End Sub

Now I don’t think its the weapons sub as when I try to call it with a button that just has Call Weapons in it, it works fine. I wasn’t expecting this to be so difficult lol.

Ive tried a couple different versions of the Change macro to no effect.
Ive tried putting this in every page including a new module and This Workbook and couldnt even get it to display and of the Debug messages

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("E2")) Is Nothing Then
        Debug.Print "Change in E2 detected"

        Dim selectedCell As Range
        Set selectedCell = Selection

        Application.EnableEvents = False
        Debug.Print "Calling Weapons"
        Call Weapons
        Application.EnableEvents = True

        selectedCell.Select
    End If
End Sub

I did the same thing with the same results with this

Private Sub Worksheet_Calculate()
    Debug.Print "Calculate event triggered"

    Dim selectedCell As Range
    Set selectedCell = Selection

    Application.EnableEvents = False
    Debug.Print "Calling Weapons"
    Call Weapons
    Application.EnableEvents = True

    selectedCell.Select
End Sub

And I’ve done the same thing with this

Private Sub Worksheet_Calculate()
    Static oldVal As Variant
    Dim newVal As Variant

    newVal = Me.Range("E2").Value

    If newVal <> oldVal Then
        Debug.Print "Change in E2 detected"

        Dim selectedCell As Range
        Set selectedCell = Selection

        Application.EnableEvents = False
        Debug.Print "Calling Weapons"
        Call Weapons
        Application.EnableEvents = True

        selectedCell.Select
    End If

    oldVal = newVal
End Sub

These are all just potential solutions I found while googling. But sadly nothing seems to work.