First, both VBA Project and worksheet/workbook protection (via tools|protection
dialog) is very easy to break. So all your techniques (including the following)
can still fail with an experienced user--or a dedicated snooper.

But you may want to consider another alternative.

Give the that workbook a nice password to open. But don't share it with anyone
(except your out-of-office backup person).

Then create a dummy workbook that opens the workbook automatically when it's
opened. If the user disables macros, then the code in the dummy workbook can't
open the real workbook. (Have some notes on the first worksheet explain that if
the message doesn't go way, then they should close this workbook and open it
with macros enabled. If macros are enabled, then this message will disappear
pretty quickly.)

If the user allows macros, then the dummy workbook opens the real workbook and
closes itself.

This is what the code in the dummy workbook could look like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="\\path\path\path\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

or

Option Explicit
Sub auto_open()
Workbooks.Open Filename:=thisworkbook.path & "\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

Remember to protect that project, too--else someone may see the password to the
real workbook.

LAF wrote:

In my file, when they enable macros, I have code that protects the
sheets so that they can't change them. The problem is that if they
open the file and disable macros, we don't want them to make any
changes to the sheets and then save the file. That is why I need to
have a pw or something when the open the file, so that if they disable
macros, they would need the pw (but we wouldn't give it to our users to
prevent them from making changes to the file). When I enable macros, I
want code that automatically enter the pw for them.

--
LAF
------------------------------------------------------------------------
LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656
View this thread: http://www.excelforum.com/showthread...hreadid=482271

--

Dave Peterson