Article: Force User To Enable Macros

[Ken Puls]

You can view the page at http://www.excelguru.ca/forums/conte...-Enable-Macros

[BKunkle]

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.

[Ken Puls]

Interesting... I was not aware that the email code would bypass this functionality. I suppose you could probably disable the ability to send by overwriting keyboard shortcuts/disabling ribbon commands, but I haven't tried that myself.

[BKunkle]

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 should have written:
... so now the workbook is wide open and the macros are not enabled.

[Ken Puls]

I figured out what you meant.

[nite_rider]

Thanks for the code! The version that i modified works great!
The only problem that i faced was in the code below. When i tried to close the workbook, the save dialog box won't go away.
Seems like the problem is here:

If bSaved Then
ThisWorkbook.Saved = True
Cancel = True
Else
Cancel = True
End If