How to require excel 2010 workbook to be in specific filepath in order to open with VBA?

Question

I would like to require a workbook to be located in a specific file path to open. I am using VBA in excel 2010.

(Project / ThisWorkbook code: Header: Workbook / Open)

Private Sub Workbook_Open
    FilePath
    'Runs file path check when workbook opened
End Sub

(Project / Modules / Module1 Header: (General) FilePath)

Sub FilePath()

If ActiveWorkbook.Path = ("path address") Then
    PathOk
Else
    MsgBox ("This is an Unauthorized copy of this file. Please contact Administrator"), vbOKOnly
    ActiveWindow.Close SaveChanges:=False
    Exit Sub
End If
End Sub

Problem is Workbook currently opens and runs PathOk routine evertime regardless of file path.

Your code works on my end. What scenario do you encounter what you describe above? — L42

simpLE MAn simpLE MAn · Accepted Answer · 2014-04-02T02:50:05

To be sure you are checking the right workbook maybe you should do it like this:

In the "Thisworkbook" code module:

Private Sub Workbook_Open()
    FilePath Me
End Sub

In a standard code module:

Sub FilePath(WB As Workbook)

    If WB.Path = ("path address") Then
        PathOk
    Else
        MsgBox "This is an Unauthorized copy of this file. Please contact Administrator", _
                vbOKOnly + vbCritical 'So it looks more critical ;)

        ActiveWindow.Close SaveChanges:=False
        Exit Sub
    End If

End Sub

How to require excel 2010 workbook to be in specific filepath in order to open with VBA?

1 Answers