locked
Force Macros to be enabled RRS feed

  • Question

  • Much has ben written about disabling macros. I want to do just the opposite: prevent any Excel operations unless macros are enabled.
    Wednesday, March 27, 2013 8:42 AM

Answers

  • Unless you have admin rights for the user there's nothing you can do to prevent the user interacting with Excel, and quite rightly too!

    However if you only want to prevent interaction with your own file you can do something like this -

    In the workbook's save event hide all sheets except an instruction sheet that explains macros must be enabled to use the workbook.

    In the workbook's open event unhide all sheets and hide the instruction sheet

    Peter Thornton

    • Marked as answer by JonWoellhaf Wednesday, March 27, 2013 10:07 AM
    Wednesday, March 27, 2013 9:02 AM

All replies

  • Unless you have admin rights for the user there's nothing you can do to prevent the user interacting with Excel, and quite rightly too!

    However if you only want to prevent interaction with your own file you can do something like this -

    In the workbook's save event hide all sheets except an instruction sheet that explains macros must be enabled to use the workbook.

    In the workbook's open event unhide all sheets and hide the instruction sheet

    Peter Thornton

    • Marked as answer by JonWoellhaf Wednesday, March 27, 2013 10:07 AM
    Wednesday, March 27, 2013 9:02 AM
  • Excellent! Thank-you, Peter.

    I put the hide sheets in the Before Close event rather than the Save event. Do you see a problem with that?

    Jon

    Wednesday, March 27, 2013 10:07 AM
  • No, putting the hide sheets in the before close does nothing useful, it needs to go in the before save. You might want to include additional code to reshow the sheets and hide the instruction sheet after saving so the user can continue working with it (if only saving but not save & close), but be sure to set the workbook's .Saved = true to prevent a potentially unnecessary save prompt when closing.

    Peter Thornton

    Wednesday, March 27, 2013 11:56 AM
  • Of course. Thanks again, Peter.

    Jon

    Wednesday, March 27, 2013 9:07 PM