Hi all,
I'm fairly new to Excel VBA, and am trying to protect a workbook as much as I can.
I've been able to get this VBA code working (which gives the user a warning message if the workbook has been copied to another location; the file then automatically closes, rendering the workbook unusable):
Sub Auto_Close()
Dim FileLoc, MyPath As String
FileLoc = ThisWorkbook.Path
MyPath = "/Users/user1/Desktop"
If FileLoc <> MyPath Then
MsgBox ("File Not in Location")
Application.DisplayAlerts = False
ThisWorkbook.Close
End If
End Sub
Display More
What I'm trying to do is append a macro that then deletes the excel book once it has auto-closed. Something like:
Sub Close_Delete()
Dim FileLoc, MyPath As String
FileLoc = ThisWorkbook.Path
MyPath = "/Users/user1/Desktop"
If FileLoc <> MyPath Then
MsgBox ("File Not in Location")
ThisWorkbook.Close
ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End If
End Sub
Display More
However, the trouble I'm having is...I can't get these two to work together quite as smoothly as I would like. When I run the Close_Delete sub, there will be a "File Not in Location" prompt, at which point a prompt appears asking if I want to save changes before the workbook closes. The interesting thing is...whether I select "Cancel", "Save", or "Don't Save", the file closes and deletes itself.
I'm just wondering how I could revise the Close_Delete sub so that the pop-up box does not appear at all, and the workbook just closes and deletes itself after displaying the "File Not in Location" message.
Any and all suggestions are welcome. Really having a tough time with this one!