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
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
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!