CODEWITHSUNDEEP
excelvba
user569125
user569125

Reputation: 1463

Excel VBA: Enabling Macro Settings

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?

Upvotes: 9

Views: 68571

Answers (4)

Tomamais
Tomamais

Reputation: 95

There is a way to automate the macro enabling, but you need to have full control on client machine at least once. Use digital certificates and sign your macros with it, the first time you run macros signed, Excel will offer to you an option to always trust macros signed by this certificate. When you trust it, all macros signed with your certificate will be automatically enabled.

This is the best option to develop and manage macros on your corporate network.

Upvotes: 1

assylias
assylias

Reputation: 328835

This is a security feature. If you could disable it within Excel in vba it would defeat the purpose. I'm afraid the answer is no, the user will need to adapt his security settings...

That being said, you could change the settings from another application by modifying the registry. On recent versions of Office, this is the key that defines the behaviour: HKEY_CURRENT_USER\Software\Microsoft\Office\XXXX\Excel\Security where you need to modify (or create) a DWORD named vbawarnings. The values it can take are as follows (source):

  • 1: Enable all macros (not recommended)
  • 2: Disable all with notification
  • 3: Disable all except digitally signed macros
  • 4: Disable all without notification

Upvotes: 4

nhinkle
nhinkle

Reputation: 1157

Here is an alternate method to check if VBA is enabled without having to hide/show sheets. The problem with hiding/unhiding sheets is that the worksheet could be saved in an "enabled" state, be closed without saving, and then be re-opened and not display the warning.

Create the following function in a Module (not a worksheet or workbook macro):

Public Function MacrosEnabled()
    MacrosEnabled = True
End Function

Now, in whatever cell you want to display a notice regarding whether macros are enabled or not, enter the following equation:

=IF(ISERROR(MacrosEnabled()&NOW()),"Macros are disabled","Macros are enabled")

If you just use ISERROR(MacrosEnabled()) the value is not recalculated every time the spreadsheet is opened. By concatenating NOW(), the current time is read every time the spreadsheet is opened, and so the value of the cell is recalculated. I tested this method extensively, and it seems to very reliably indicate whether macros are enabled or not.

You can then apply conditional formatting to do a number of things if macros are disabled:

  • Highlight the check-cell in a bright color, and perhaps change the "macros are disabled" text to include instructions on how to enable macros.

  • "Hide" cells whose values depend on macros being enabled by setting their text color and background color to be the same.

Here is a spreadsheet that demonstrates this method.

Upvotes: 4

brettdj
brettdj

Reputation: 55692

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

Upvotes: 14

Related Questions