Results 1 to 6 of 6

Thread: Article: Force User To Enable Macros

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14

    Article: Force User To Enable Macros



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    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.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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.

  4. #4
    Quote Originally Posted by BKunkle View Post
    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.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    I figured out what you meant.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •