Although tricks like this allow you to lock down the workbook until macros are enabled, they can all be defeated if the user emails the workbook from within Excel once it has been opened and macros enabled. Typically, the recipient's computer will have macros disabled by default, so now the workbook is wide open and the macros are enabled.
I have not been able to find a solution to that problem. There appears to be no event triggered by the sending of email and it is difficult to disable the send command in Excel 2007.
However, I did work out a very nice reminder to enable macros that you can automate for users. It's quite simple:
In the workbook open event, write the time and date in an otherwise unused cell. You might also set the foreground and background colors to be the same (so no one sees this time and date).
ThisWorkbook.Worksheets(1).Range("M1") = Now
Then, in any other unused cell, place this formula:
=IF((NOW()-M1) > 0.01,"Enable Macros!!","")
Format this cell as bold, red on white (or however you like). You can do is in multiple unused cells to more effectively warn your users.
Change M1 to whatever cell you like. The 0.01 means one hundredth part of a day or 14.4 minutes.
Bookmarks