VBA To Close Workbook Based On Cell Value Change And Open New Workbook

[Excel Fox]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "C2" Then
        Select Case InStr(1, "|" & Join(Application.Transpose(Range("E1:E150").Value2), "|") & "|", "|" & Range("C2").Value2 & "|") > 0
            Case True
                Open_Doc
            Case Else
                'Do something / nothing
        End Select
    End If
    
End Sub


Sub Open_Doc()

    Dim var As Variant
    Dim wbk As Workbook
    Dim varWbkList As Variant
    
    varWbkList = Application.Transpose(Range("E1:E150").Value2)
    Application.ScreenUpdating = False
    For Each var In varWbkList
        On Error Resume Next
        Set wbk = Workbooks(CStr(var))
        Err.Clear: On Error GoTo 0: On Error GoTo -1
        If Not wbk Is Nothing Then
            wbk.Close True 'Use True/False to save changes or not
            Set wbk = Nothing
        End If
    Next var
    Workbooks.Open Sheets("Sheet1").Range("B2").Value & Sheets("Sheet1").Range("C2").Value
    Workbooks("Main.xls").Activate
    Application.ScreenUpdating = True

End Sub