Closing a workbook if it is not located in a specific directory

[willow1985]

Hello, I know that it is not possible to prevent someone from copying an excel workbook however I was wondering if it was possible to disable the copied workbook?
That if the sheet is opened from a different location than the original (I can specify the directory in a cell on a hidden sheet) it will close down with a message like "This is a copy of the original workbook and therefore cannot be used. Please open the correct workbook" and close the file.

Is this possible? I have a spreadsheet at work that many users utilize but apparently at one point an individual copied the sheet and now we have overlapping records. It is a mess and I am trying to prevent reoccurance.
Thank you to anyone who can help!

 

[JGordon11]

in the ThisWorkbook module:

Private Sub Workbook_Open()
    Const AllowCancel = True 'when true, allow the user to exit the messagebox by clicking Cancel and keep file open
    Const FullFileName = "replace this text between the quotes with full path and file name"
    With ActiveWorkbook
        If .FullName <> FullFileName Then
            Dim choice As Long, bttns As Long
            If AllowCancel Then bttns = vbOKCancel Else bttns = vbOKOnly
            choice = MsgBox("This is a copy of the original workbook and therefore cannot be used. Please open the correct workbook.", bttns)
            If choice = vbOK Then .Close
        End If
    End With
End Sub

Replace the text in the Const FullFileName ... line with what the immediate window returns when you type

?ActiveWorkbook.FullName

I recommend leaving AllowCancel set to true so that you have an option to still access the file, for instance if you intentionally move it to another folder or rename it.

 

[Eric W]

I recommend leaving AllowCancel set to true so that you have an option to still access the file, for instance if you intentionally move it to another folder or rename it.

But if you leave AllowCancel to true, that allows an unauthorized user to move and use the workbook in another folder, just by clicking the Cancel button. Seems safer to leave that option disabled, and if Willow1985 needs to change that, she can open the workbook with macros disabled, then edit the macro with the new folder location and save it.

Which raises another point: Is the data entry done by macros, or by manual entry? If by manual entry, then the end user can simply disable macros and enter whatever he wants. You may need to hide (xlVeryHidden) the worksheets, and unhide them if the workbook is opened in the right folder. Raising yet another point: Excel is pretty poor at security. It's basically only good enough to keep out casual users. So be aware that if someone really wants to move it and use it, they can probably find a way.

 

[JGordon11]

Then maybe like this

Private Sub Workbook_Open()
    Const FullFileName = "replace this text between the quotes with full path and file name"
    With ActiveWorkbook
        If .FullName <> FullFileName Then
            Dim choice As Long, bttns As Long
            If Application.UserName = "Willow" Then bttns = vbOKCancel Else bttns = vbOKOnly
            choice = MsgBox("This is a copy of the original workbook and therefore cannot be used. Please open the correct workbook.", bttns)
            If choice = vbOK Then .Close
        End If
    End With
End Sub

Replace "Willow" with your Application.Username

 

[willow1985]

But if you leave AllowCancel to true, that allows an unauthorized user to move and use the workbook in another folder, just by clicking the Cancel button. Seems safer to leave that option disabled, and if Willow1985 needs to change that, she can open the workbook with macros disabled, then edit the macro with the new folder location and save it.

Which raises another point: Is the data entry done by macros, or by manual entry? If by manual entry, then the end user can simply disable macros and enter whatever he wants. You may need to hide (xlVeryHidden) the worksheets, and unhide them if the workbook is opened in the right folder. Raising yet another point: Excel is pretty poor at security. It's basically only good enough to keep out casual users. So be aware that if someone really wants to move it and use it, they can probably find a way.

The individuals using the workbook made a copy in error and are not very familiar with excel. They also would probably ignore warnings so I will most likely disable the option to cancel. The workbook already has macros in it but if it is moved by accident I would have to disable macros to fix it.

Also multiple users will be using the workbook so it cannot be specific to one user. Not sure what this line means....
If Application.UserName = "Willow" Then bttns = vbOKCancel Else bttns = vbOKOnly

 

[willow1985]

in the ThisWorkbook module:

Private Sub Workbook_Open()
    Const AllowCancel = True 'when true, allow the user to exit the messagebox by clicking Cancel and keep file open
    Const FullFileName = "replace this text between the quotes with full path and file name"
    With ActiveWorkbook
        If .FullName <> FullFileName Then
            Dim choice As Long, bttns As Long
            If AllowCancel Then bttns = vbOKCancel Else bttns = vbOKOnly
            choice = MsgBox("This is a copy of the original workbook and therefore cannot be used. Please open the correct workbook.", bttns)
            If choice = vbOK Then .Close
        End If
    End With
End Sub

Replace the text in the Const FullFileName ... line with what the immediate window returns when you type

?ActiveWorkbook.FullName

I recommend leaving AllowCancel set to true so that you have an option to still access the file, for instance if you intentionally move it to another folder or rename it.

for FullFileName is it possible to have a cell reference with the data? How would that look?
The individuals using the workbook would not go out of their way to unhide any sheets and would make it easier to change a location if need be by using a cell reference in a hidden sheet.

 

[JGordon11]

If you had a hidden sheet named FileName with a range named FullFileName containing the string with full path and name then

replace the Const FullFileName line with

Dim FullFileName as string
FullFileName = Sheets("FileName").Range("FullFileName").Value

I do recommend leaving AllowCancel as true until you have set up and tested it and everything is working. Then change it to false and save the file.

 

[willow1985]

If you had a hidden sheet named FileName with a range named FullFileName containing the string with full path and name then

replace the Const FullFileName line with

Dim FullFileName as string
FullFileName = Sheets("FileName").Range("FullFileName").Value

I do recommend leaving AllowCancel as true until you have set up and tested it and everything is working. Then change it to false and save the file.

I did the below code, however I get a Compile error: Constant expression required on the "Const FullFileName = Sheets("List").Range("F1").Value" Line

Private Sub Workbook_Open()
    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True
    Dim FullFileName As String
    Const AllowCancel = True
    Const FullFileName = Sheets("List").Range("F1").Value
    With ActiveWorkbook
        If .FullName <> FullFileName Then
            Dim choice As Long, bttns As Long
            If AllowCancel Then bttns = vbOKCancel Else bttns = vbOKOnly
            choice = MsgBox("This is a copy of the original workbook and therefore cannot be used. Please open the correct workbook.", bttns)
            If choice = vbOK Then .Close
        End If
    End With
End Sub

 

[Joe4]

Contstant variables cannot be set to things that can change, like cell references.
That goes against the whole premise of what a "Constant" is.

Did you mean just to make it a regular variable?
Or perhaps a public variable?

 

[willow1985]

Contstant variables cannot be set to things that can change, like cell references.
That goes against the whole premise of what a "Constant" is.

Did you mean just to make it a regular variable?
Or perhaps a public variable?

I forgot to remove the Constant so fixed that.

Now I have another problem where it is giving me the MsgBox even though the file IS in the correct directory.

Batch Log.xlsm
	E	F
1	File Directory:	X:\ADMINISTRATION\QAEng\Carla's Documents\Test\
2
3		X:\ADMINISTRATION\QAEng\Carla's Documents\Test\
List

Cell Formulas
Range	Formula
F3	F3	=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)