Need to run auto_open before user disables macros at startup

[pinkfloydfan]

Hi guys

I have a spreadsheet to which I have added the following code in the
auto_open routine:

Sub auto_open()
Dim mydate As Date
mydate = "10-Nov-2006"
If mydate < today Then
MsgBox ("Project expired on " & mydate & vbCrLf & "Press OK to
exit")
ActiveWorkbook.Close
End If
End Sub

The point of this is to add an expiry time to the spreadsheet such that
if today>mydate then the workbook is closed and it can no longer be
used.

Unfortunately, if the user has macro security set to medium then they
get the option of disabling the macros before running the auto_open
routine...does anybody know a better way to deal with this for me
please?

 

[paul.robinson]

Hi
Macro security is at the discretion of the user - what if your macro
was a virus? So you have no chance of getting round that inside the
document.
In your before_close macro you could lock all the sheets and protect
the workbook so that nobody could change anything when they open. In
your auto_open you could unfreeze everything unless you meet your date
criterion. So the workbook is useless by default unless macros are
enabled.
regards
Paul

 

[pinkfloydfan]

Thanks Paul

Sorry for the late response.

I decided on a slightly different workaround which might be useful to
other people. These are the steps I took:

1) create a front sheet ("Startup") which displays a message "You must
enable macros"
2) write an auto_close() Sub which which calls the Sub
Hideworksheets(). This makes all other sheets except "Startup" have
the property xlVeryHidden

Sub Hideworksheets()
ThisWorkbook.Worksheets("Startup").Visible = True
For Each Sheet In ThisWorkbook.Worksheets()
If Sheet.Name <> "Startup" Then Sheet.Visible = xlVeryHidden
Next Sheet
End Sub

3) write an auto_open Sub which calls the Sub Showworksheets(). This
checks the date and if it is still in date then makes all other
worksheets have the property Visible

Sub Showworksheets()
For Each Sheet In ThisWorkbook.Worksheets()
If Sheet.Name <> "Startup" Then Sheet.Visible = True
Next Sheet
ThisWorkbook.Worksheets("Startup").Visible = xlVeryHidden
End Sub

Sub auto_open()
Dim mydate As Date
mydate = "10-Nov-2006"
If mydate < today Then
MsgBox ("Project expired on " & mydate & vbCrLf & "Press OK to
exit")
ActiveWorkbook.Close
End If
Call Showworksheets
End Sub

4) Be sure to Lock the sheets and only unlock the cells you want the
user to input to. I would also suggest that you hide the formulae in
all cells.

5) Password protect the Modules.

One other thing...save a version without the date check for yourself
otherwise the whole spreadsheet will be useless after the expiry date.

All the best
Lloyd