Excel

Force User to Enable Macros

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

If a user does not have macros enabled, when the workbook is opened a sheet prompting them to enable macros is shown, all other sheets are hidden. 

Discussion:

When a workbook contains VBA procedures it is important that macros be enabled. This procedure forces the user to enable macros. (Upgraded 14 November 2006 to cater for chartsheets as well as worksheets) 

Code:

instructions for use

			

Option Explicit Private Sub Workbook_Open() With Application 'disable the ESC key .EnableCancelKey = xlDisabled .ScreenUpdating = False Call UnhideSheets .ScreenUpdating = True 're-enable ESC key .EnableCancelKey = xlInterrupt End With End Sub ' Private Sub UnhideSheets() ' Dim Sheet As Object ' For Each Sheet In Sheets If Not Sheet.Name = "Prompt" Then Sheet.Visible = xlSheetVisible End If Next ' Sheets("Prompt").Visible = xlSheetVeryHidden ' Application.Goto Worksheets(1).[A1], True '< Optional ' Set Sheet = Nothing ActiveWorkbook.Saved = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False Call HideSheets .ScreenUpdating = True .EnableCancelKey = xlInterrupt End With End Sub Private Sub HideSheets() ' Dim Sheet As Object '< Includes worksheets and chartsheets ' With Sheets("Prompt") ' 'the hiding of the sheets constitutes a change that generates 'an automatic "Save?" prompt, so IF the book has already 'been saved prior to this point, the next line and the lines 'relating to .[A100] below bypass the "Save?" dialog... If ThisWorkbook.Saved = True Then .[A100] = "Saved" ' .Visible = xlSheetVisible ' For Each Sheet In Sheets If Not Sheet.Name = "Prompt" Then Sheet.Visible = xlSheetVeryHidden End If Next ' If .[A100] = "Saved" Then .[A100].ClearContents ThisWorkbook.Save End If ' Set Sheet = Nothing End With ' End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select View/Project Explorer
  4. Select the ThisWorkbook module
  5. Copy and paste the code above into this Module
  6. Now select File/Close and Return To Microsoft Excel
  7. Dont forget to save your changes...
  8. Name one of your sheets "Prompt"
 

Test the code:

  1. If you have macros enabled, disable them then close and open the workbook.
 

Sample File:

ForceMacros_New.zip 10.29KB 

Approved by mdmackillop


This entry has been viewed 1297 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express