Enable/Disable Macro Box

[lostramc]

Is there any way to automatically respond "Enable Macros" without the user even having to see and/or respond to this dialog box?
I know I could change the security settings to "Low", but I don't want to do that.

[/img]

 

[Oaktree]

Not exactly (that would defeat the purpose of that security feature).

However, you might look into digital signatures. If the user has added you (or whoever the author of the VBA is) as a trusted source and the code is signed, s/he won't have to choose to enable macros.

 

[KenCriss]

Also, if you save your workbook as an add-in, your user won't have to deal with that dialog box I believe.

Not exactly (that would defeat the purpose of that security feature).

However, you might look into digital signatures. If the user has added you (or whoever the author of the VBA is) as a trusted source and the code is signed, s/he won't have to choose to enable macros.

 

[lostramc]

I looked into the digital signature business, but either I was doing it wrong or our facility has something disabled. I could not get a signature attached to it.

 

[Mister H]

I am not good at VBA but I google your question and came up with a little workaround. This will not get rid of the message but it will prevent the User from using your sheet if they Do Not Enable Macros.

Here is the link to what I am referring to:
http://www.dotxls.com/excel-security/28/how-to-make-a-user-enable-excel-macros

And this is the code and instructions posted there:

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 disabled

Medium Security (recommended) User is prompted on each file-open to enable or disable Excel macros

Low 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.xls

Copy this code to the ThisWorkBook macro sheet:

Private Const dsWarningSheet As String = "sheet1" 'Enter name of the Entry/Warning Page

	Private 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
    Next
End Sub

	Private 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 If
End Sub

	Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
      ds.Visible = True
      Next
ActiveSheet.Visible = xlVeryHidden
End Sub

May not be what you are looking for but it may give you some ideas.

Good Luck,
Mark