So we have a legacy spreadsheet that isn’t going anywhere anytime soon. I’ve maintained two of them because there are times when we need different code to import a job. Upon opening the sheet, it attaches either one called Shipper.xla or on the second sheet, Shipper_Project.xla.

I’d like to just have on workbook to handle both XLA files depending on the job…so before it attaches, I created a MsgBox to ask if it is a Project Job? If yes, it attaches the Shipper_Project.xla, if no, it attaches the shipper.xla. If I hit yes, it works but still attaches the Shipper.xla.

    result = MsgBox("Is This An MBS Project Job?", vbYesNo)
    If result = vbYes Then AttachXLAProject
    If result = vbNo Then AttachXLA

    Sub AttachXLAProject()
    With CreateObject("WScript.NetWork")
        If UCase(.UserDomain) = "PINNSTR" Then
            strFilePath = "\fileserverUtility$InternalShipperProjectCodeShipper_Project.xla"
        Else
            strFilePath = "\OMAAC-ServerPinnacleShipperShipper.xla"
        End If
        ActiveWorkbook.VBProject.References.AddFromFile strFilePath
    End With
End Sub

Sub AttachXLA()
    With CreateObject("WScript.NetWork")
        If UCase(.UserDomain) = "PINNSTR" Then
            strFilePath = "\fileserverUtility$InternalShipperCode 3.0Shipper.xla"
        Else
            strFilePath = "\OMAAC-ServerPinnacleShipperShipper.xla"
        End If
        ActiveWorkbook.VBProject.References.AddFromFile strFilePath
    End With
End Sub

Now, in theory, I can name them both shipper.xla since they are saved in different parts of the file server, but I still need the selection to be made at start. I’ve tried ElseIf and EndIf and it just makes it worse for some reason.

Any suggestions?