Excel VBA: Enabling Macro Settings

Question

I wrote a macro method to auto-size the columns in an Excel sheet upon opening the sheet. However, this solution does not work in the case that the user has not enabled macros in Excel.

Is there any way to check if the user has VBA macros disabled in Excel, and then enable the macros with Excel VBA code?

Answer 1

The standard way of doing this is to force the user to enable macros by use of a splash screen.

• All the sheets in the workbook except for a spash screen are made very hidden (which can only be changed via VBA or the VBA editor)
• if macros are enabled:
  1) When the workbook opens, the code unhides all these very hidden sheets
  2) When the workbook closes then all these sheets are made very hidden again
• If macros are not enabled then the user can only see the Splash Screen saying "Please enable macros, close and then re-open this file"

Two links with full code for this technique are listed below

1. Brad Yundt covers this here at TekTips
2. Jonske at VBAeXpress