How to make a user enable Excel macros


Users can set thier Excel Security to High, Medium or Low.Click on Tools-Macros-Security to set your Security level.High Security All Excel macros (except from trusted sources)are disabledMedium Security (recommended) User is prompted on each file-open to enable or disable Excel macrosLow Security (not recommended) Excel Macros can run automatically without a prompt. You are not protected from potentially unsafe macros.Developer''s cannot control a user''s security settings and cannot force a user to run macros without seeing the pop-up security warning.If you wish users to enable macros you can make the Excel file unusable unless they are enabled. All sheets (except one) can be hidden unless the user enables macros.In the example below all sheets (except one) are hidden when the file is saved or closed.When the file is opened with Excel macros disabled the user will only see 1 sheet with a warning message.ie in cell b10: "Excel Macros must be enabled to use the workbook. Please close and reopen this file with Macros enabled"When the Excel file is opened with macros enabled the hidden worksheets will be automatically unhidden and the warning sheet will be hidden.When the Excel file is saved, all sheets except one are hidden.If the user saves without closing then the sheets remain hidden until the cursor is moved on the visible sheet. Note: Sheets are hidden on file-saving not file-closing.I used this logic as a user may save a workbook without closing. This would leave a copy of the saved workbook with unhidden sheets on the drive which others could open in read-only mode.Click here for an example file with macros: MakeUsersEnableMacros.xlsCopy this code to the ThisWorkBook macro sheet:

Private Const dsWarningSheet As String = "sheet1" ''Enter name of the Entry/Warning PagePrivate Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean,  Cancel as Boolean)For Each ds In ActiveWorkbook.Sheets        If LCase(dsWarningSheet) = LCase(ds.Name) Then        ds.Visible = True        Else        ds.Visible = xlVeryHidden        End If    NextEnd SubPrivate Sub Workbook_SheetSelectionChange(ByVal ds As Object, ByVal Target As Excel.Range)If LCase(ds.Name) = LCase(dsWarningSheet) Then      For Each ds In ActiveWorkbook.Sheets            ds.Visible = True            Next      ActiveSheet.Visible = xlVeryHidden      End IfEnd SubPrivate Sub workbook_open()Sheets(dsWarningSheet).SelectFor Each ds In ActiveWorkbook.Sheets      ds.Visible = True      NextActiveSheet.Visible = xlVeryHiddenEnd Sub

', '



dotxls.com
"Yes, Excel can do that!"
© 2000-2014 All rights reserved.