auto close

[imranrashid]

Today is 28.MAR.2002
A1 is 27.mar.2002

if file is open today it should not work in anyway but just close.

how to do it?

[dk]

Hi,

The way to do this is with a little VBA code. You need to open the workbook code module - to do this right click the lower of the 2 Excel icons at the top left of the screen. You should see an option which says View Code - choose it. Now paste this code:-

Private Sub Workbook_Open()
If Date > Sheets("Sheet1").Range("A1") Then
ThisWorkbook.Close False
End If
End Sub

That's the first part sorted. However, this code won't work if the user disables macros. There is no entirely satisfactory solution to getting around this but here's a commonly used method:

Insert an additional sheet in your workbook and include a message on it saying something like Macros must be enabled to use this workbook. Name the sheet Macros Disabled. Then run this code in a standard module:-

Sub HideSheets()
Dim sht As Object

For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then
sht.Visible = xlSheetVeryHidden
End If
Next
End Sub

This will hide your sheets so that they cannot be hidden with Format, Sheet, Unhide. The only way to unhide them is with code.

Now save your file. Then in the workbook code module (same place you put the first procedure) replace the old one with these 2 procedures:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Object

Sheets("Macros Disabled").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then
sht.Visible = xlSheetVeryHidden
End If
Next
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Object
If Date > Sheets("Sheet1").Range("A1") Then
ThisWorkbook.Close False
End If
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next
Sheets("Macros Disabled").Visible = xlSheetVeryHidden
End Sub


You will now find that if the user opens the file with macros disabled the only thing they'll see is a worksheet with your 'Macros must be enabled blah blah' message. If they enable macros then that sheet will be hidden and the other sheets will be made visible.

Long winded, yes! Pain in the a*s!, yes!

It's probably experimenting with this code on a test workbook so you understand what's happening before doing something nasty to your real workbook.

HTH,
Dan