Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to run auto_open before user disables macros at startup
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 todaymydate 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to run auto_open before user disables macros at startup
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 wrote: 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 todaymydate 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to run auto_open before user disables macros at startup
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom User Form At Startup | Excel Discussion (Misc queries) | |||
Macros disables under medium security | Excel Programming | |||
Bypassing startup macros | Excel Programming | |||
Bypassing startup macros | Excel Programming | |||
disabling Auto_Open macros | Excel Programming |