Auto Delete Workbook After Auto-Close

  • 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):

    What I'm trying to do is append a macro that then deletes the excel book once it has auto-closed. Something like:



    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!

  • Hello,


    The main question is around your exact objective :

    Quote


    am trying to protect a workbook as much as I can ...


    ... because the full 100 % protection is actually impossible ... given the number of methods which exist ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Regarding the logic of your second macro ...


    You can only Kill file ... and completely delete it ... once it has been saved ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • You cannot save a workbook after closing.


    Code
    1. ThisWorkbook.Close
    2. ThisWorkbook.Saved = True
    3. ThisWorkbook.ChangeFileAccess xlReadOnly
    4. Kill ThisWorkbook.FullName

    Try this


    Code
    1. With ThisWorkbook
    2. .Saved = True
    3. .Close
    4. Kill .FullName
    5. End With


    Such Code can easily be averted simply by not enabling macros. If you need that much security don't use Excel

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this



    https://www.excelguru.ca/content.php?184

  • CROSS-POSTING ... And ... deliberately spare on Feedback ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)