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

  • Post by Darko_Giac ().

    This post was deleted by the author themselves ().