how to delete a workbook automatically by macro?

[FIFAWORLDCUP]

I have an xlsm file,hope to delete itself automatically after the date 2016/9/15,how to write the macro?

thanks in advance

 

[Michael M]

Have a look here....BUT....remember

1. this is permanent and cannot be undone
2. If someone disables macro before opening....it won't work !!


Creating an MS Excel file which deletes itself when a certain time or date is reached - Stack Overflow

 

[FIFAWORLDCUP]

<code>Private Sub Workbook_Open()
If Now() > #1/1/2014# Then Call SuicideSub
End Sub</code>  '
  <code>Sub SuicideSub()
'courtesy Tom Ogilvy
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub</code>

i have got it.

2. If someone disables macro before opening....it won't work !!

have the method that do not allow somebody disables macro before opening?

 

[Joe4]

have the method that do not allow somebody disables macro before opening?

You cannot, and thank goodness you cannot, or virus makers would have a field day with this (if they could force you to enable malicious code).

What I have seen some people do is hide everything in the workbook and password protect. So they only way they can see anything or use the data is to enable macros, which they unhides the stuff for them.

Just curious, why do you want a self-destructing file?

 

[FIFAWORLDCUP]

have the method that do not allow somebody disables macro before opening?

----------------------------------------------------------------------------------
In my job a lot of excel files which contains macro code,using these code can print automatically,create worksheets automatically and so on....(these code written by other people),but some other sematers changed these files format after their using,these will spending my time copying code to code window again.so,how to disable others changing the format of excel files(xlsm format only)?how to forbid others changing the format of excel files besides xlsm,how to write the code ,thanks in advance!


why do you want a self-destructing file?

just to look at the power of vba,do not other using!

 

[Michael M]

Create a Sheet in your workbook called INTRO
Put a textbox on that sheet that explains to the user that they must enable macros to use the workbook
THEN use BOTH of these codes to force them to enable macros !!

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets("INTRO").Activate
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "INTRO" Then ws.Visible = xlSheetVisible
        If ws.Name = "INTRO" Then ws.Visible = xlSheetHidden
    Next ws
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
        If ws.Name = "INTRO" Then ws.Visible = xlSheetVisible
        If ws.Name <> "INTRO" Then ws.Visible = xlSheetHidden
Next ws
End Sub

 

[IvanYerk]

Michael M - I came across this thread and hoping to get some clarity on the code you posted. I'm a VBA novice - do I put this code into one module or split into two modules? I've tried it both ways and I still can't get it to execute. Or if the intervening years you have a better way to force the user to enable macros, that'd work for me as well. Thanks for your help!

 

[Joe4]

Michael M - I came across this thread and hoping to get some clarity on the code you posted. I'm a VBA novice - do I put this code into one module or split into two modules? I've tried it both ways and I still can't get it to execute. Or if the intervening years you have a better way to force the user to enable macros, that'd work for me as well. Thanks for your help!

What Michael posted is Workbook event procedure code. In order for this code to run autoamtically, it MUST be placed within the pre-defined "ThisWorkbook" module in that workbook. If you place it in any other module, it will not work.

 

[IvanYerk]

Perfect! That did the trick!

 

[IvanYerk]

Oops spoke too soon - when I close the workbook, works fine I can see the sheets get hidden. But when I re-opened, I was sent to Debugger with this line from the first code highlighted:
If ws.Name = "INTRO" Then ws.Visible = xlSheetHidden

Any ideas?