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

Sun, 15 Apr 2007 06:44:04 -0700 from Tan
<Tan@discussions.microsoft.com>:

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.)