Force users to enable macros when open a workbook

[Tan]

Hi All,

I have wrote a program for reporting dashboard and faces 2 issues. One is
end users macros security is set at high level. Second issue is end users
disable macros. How do i force end users to enable my macros if any of the
above occurs?

Can anyone advice me how do i add the extra codes to make this happen. My
codes stored in My Workbook as follows:


Private Sub Workbook_Open()
Call Login
MsgBox "Last Updated on Wednesday, February 28, 2007.", vbInformation
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

Thanks in advance...

 

[Dave Peterson]

There's nothing you can do in the workbook that will force the user to open with
macros enabled. (If macros are disabled, then none of your code will work.)

You could create a workbook that opens the real workbook. In this helper
workbook, you have one visible sheet--it contains "Please open this file with
macros enabled". If the user doesn't enable macros, then the real workbook
won't be opened.

If macros are enabled, then the first workbook opens the second (with macros
enabled), and closes itself.

Option Explicit
Sub auto_open()

Dim myPWD As String
Dim wkbk As Workbook
myPWD = "hi"
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
Password:=myPWD)

wkbk.RunAutoMacros which:=xlAutoOpen

'ThisWorkbook.Close savechanges:=False

End Sub

When you're done testing, uncomment that last line. It closes the helper
workbook without saving--could be a pain while you're testing.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Stan Brown]

Sun, 15 Apr 2007 06:44:04 -0700 from Tan

I have wrote a program for reporting dashboard and faces 2 issues. One is
end users macros security is set at high level. Second issue is end users
disable macros. How do i force end users to enable my macros if any of the
above occurs?

Unless you can fund and exploit an Excel security flaw, there's no
way. This the whole point of setting the macro security level, after
all. It would be pretty useless if a workbook could contain an
unsigned macro that ran despite the user setting security to High.

So you can't "force" users to run your macros. If your workbook is
something that they need, when you distribute it you can let them
know that they'll need to set macro security to Medium and then click
the Enable Macros button on the prompt.

If this is too inconvenient, you can purchase a digital signature.
Then your users will be able to select "always trust macros from this
publisher" once and never again see the macro warning. (This is how
most commercial add-ins are distributed, if they contain macros.)