Hey there - I am lost. I have a worksheet that has a dropdown list. Once I select something new from the dropdown list, a 'change' has occurred and should fire the code below. Unfortunately, when I invoke a change, I can't even get into my 'test' at the top of the code. I have the code written in the worksheet excel object and not a regular module (see photo). I have looked at the trust center settings and the Macros are set to 'Disable VBA macros with notification' and the Message Bar is set to 'Show the Message Bar in all applications when active content.....'.

Screenshot 2024-01-12 111619.png

I will also say that I simply tried opening the worksheet object and hitting F5 and I am getting 'Unexpected Error 35005'. Unfortunately, google hasn't helped me w/ that one either.

Any ideas? Thanks for the help!

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
MsgBox ("Test")

Application.ScreenUpdating = False
Application.EnableEvents = False


With Sheet3
    ClearCheck = Application.WorksheetFunction.CountA(.Range(.Cells(.Range("FirstQ").Row, 1), .Cells(.Range("FirstQ").Row, .Range("FirstQ").Column))) = 6

    If ClearCheck = True Then
        finalrow = .Cells(Rows.Count, 1).End(xlUp).Row
        finalcol = .Cells(.Range("FirstQ").Row - 1, Columns.Count).End(xlToLeft).Column
        Range(.Cells(.Range("FirstQ").Row, 1), .Cells(finalrow, finalcol)).ClearContents
    End If
End With


'Application.EnableEvents = True
'Sheets("SASB Sectors").Range("A1:E100000").AdvancedFilter Action:=xlFilterCopy, _
'            CriteriaRange:=Range("A1:C2"), CopyToRange:=Range("A4:E4"), Unique:=True
            
With Sheet3
RunCheck = Application.WorksheetFunction.CountA(.Range(.Cells(.Range("FirstQ").Row, 1), .Cells(.Range("FirstQ").Row, .Range("FirstQ").Column))) = 5

    If RunCheck = True Then
        finalrow = .Cells(Rows.Count, 1).End(xlUp).Row
        finalcol = .Cells(.Range("FirstQ").Row - 1, Columns.Count).End(xlToLeft).Column

        Application.EnableEvents = False

        .Range("FirstQ").FormulaR1C1 = _
            "=IF(VLOOKUP(RC[-2]&""_""&RC[-1],Questions!R2C3:R33C4,2,FALSE)=0,""No Question in DB"",VLOOKUP(RC[-2]&""_""&RC[-1],Questions!R2C3:R33C4,2,FALSE))"
        .Range("FirstQ").Copy
        .Range(.Cells(.Range("FirstQ").Row, .Range("FirstQ").Column), .Cells(finalrow, finalcol)).Select
        Selection.PasteSpecial xlPasteAll

        Range(.Cells(.Range("FirstQ").Row, .Range("FirstQ").Column), .Cells(finalrow, finalcol)).Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        .Range("A5").Select
        .Range(Selection, Selection.End(xlToRight)).Select
        .Range(Selection, Selection.End(xlDown)).Select
        'Rows("5:15").Select
        Selection.Rows.AutoFit

        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With

        Application.EnableEvents = True
    End If

    Application.EnableEvents = False
        .Range("A1").Copy
        .Range("A4").Select
        .Range(Selection, Selection.End(xlToRight)).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

    With Sheet3
     finalrow = .Cells(Rows.Count, 1).End(xlUp).Row
    .Range(.Cells(5, 1), .Cells(finalrow, Range("FirstQ").Column - 1)).Select

        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With

    End With
    Application.EnableEvents = True

    .Range("A2").Select
End With

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub