Can I disallow the user to open a file if he disable the macro while the file is opening?

Save your "real" workbook with a password to open (file|saveas|tools dialog).

Don't share that password.
The users won't be able to open that workbook without the password.

Create a helper workbook that opens your real workbook (and supplies the
password).

If macros are not enabled, then the helper workbook can't open your workbook.

If macros are enabled for the helper workbook, then they'll be enabled for the
real workbook.

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\realwkbk.xls", _
Password:="hi"
ThisWorkbook.Close savechanges:=False
End Sub

Remember to password protect the helper workbook's project--it'll stop users
from finding your password for the real workbook.
Robin wrote:
>
> Hi all,
>
> I don't want to apply any protection on any sheet of the file in
> advance, instead, I wrote a macro to process some jobs and also apply
> some protection in it. If the user open the file and disable the
> embedded macro while the file is opening, he can see anything in the
> file. so,
>
> How can I disallow the user to open a file if he disable the macro
> while the file is opening?
>
> Thanks,
>
> Robin

--

Dave Peterson

Hi Dave Peterson,

Good idea, it definitely works, many thanks for your feedback.

Robin.

And if you fill up the first sheet in that helper workbook with:

please open this workbook with macros enabled.
please open this workbook with macros enabled.
please open this workbook with macros enabled.
please open this workbook with macros enabled.
please open this workbook with macros enabled.

It'll be even more obvious to the users.

Robin wrote:
>
> Hi Dave Peterson,
>
> Good idea, it definitely works, many thanks for your feedback.
>
> Robin.

--

Dave Peterson

It is still some inconvenient as the user should use two files at the
same time and I should define the location for the "real" workbook.

Anyway, it's a good solution and many thanks!

Dave Peterson wrote...
>Save your "real" workbook with a password to open (file|saveas|tools
dialog).
>
>Don't share that password.
>The users won't be able to open that workbook without the password.
....

Unless they happen to have a hex editor or viewer or even the strings
utility from the GnuWin32 textutils package to find ASCII strings in
password-protected XLS files.

If you're going to put a password into a workbook, even another
workbook, you'd better do so by hardcoding the ASCII codes or using a
cyphered string.

Hi Harlan,

What does hardcoding the ASCII codes or using a cyphered string mean. Can
you please give me an example.

Thanks a lot,
Hari
India

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1107566313.143275.218850@f14g2000cwb.googlegroups.com...
> Dave Peterson wrote...
>>Save your "real" workbook with a password to open (file|saveas|tools
> dialog).
>>
>>Don't share that password.
>>The users won't be able to open that workbook without the password.
> ...
>
> Unless they happen to have a hex editor or viewer or even the strings
> utility from the GnuWin32 textutils package to find ASCII strings in
> password-protected XLS files.
>
> If you're going to put a password into a workbook, even another
> workbook, you'd better do so by hardcoding the ASCII codes or using a
> cyphered string.
>

"Hari Prasadh" <excel_hariNOSPAM@hotSPAREMEmail.com> wrote...

Hard-coded string:

Workbooks.Open Filename:="C:\foo\bar.xls", Password:="this is hard-coded"

Hard-coded ASCII codes:

p =
Array(116;104;105;115;32;105;115;32;104;97;114;100;45;99;111;100;101;100)




> What does hardcoding the ASCII codes or using a cyphered string mean. Can
> you please give me an example.
>
> Thanks a lot,
> Hari
> India
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1107566313.143275.218850@f14g2000cwb.googlegroups.com...
> > Dave Peterson wrote...
> >>Save your "real" workbook with a password to open (file|saveas|tools
> > dialog).
> >>
> >>Don't share that password.
> >>The users won't be able to open that workbook without the password.
> > ...
> >
> > Unless they happen to have a hex editor or viewer or even the strings
> > utility from the GnuWin32 textutils package to find ASCII strings in
> > password-protected XLS files.
> >
> > If you're going to put a password into a workbook, even another
> > workbook, you'd better do so by hardcoding the ASCII codes or using a
> > cyphered string.
> >
>
>

Sorry, send the last one too soon.

Hard-coded string:

Workbooks.Open Filename:="C:\foo\bar.xls", Password:="this is hard-coded"

Hard-coded ASCII codes:

p = Array(116,104,105,115,32,105,115,32,104,97,114, _
100,45,99,111,100,101,100)

w = ""
For n = LBound(p) To UBound(p)
w = w & Chr(p(n))
Next n

Workbooks.Open Filename:="C:\foo\bar.xls", Password:=w


Cyphered string:

Const FOOBAR As Byte = 67

p = "7+*0c*0c+""1'n ,'&'"

For n = 1 To Len(p)
Mid(p, n, 1) = Chr(CByte(Asc(Mid(p, n, 1))) Xor FOOBAR)
Next n

Workbooks.Open Filename:="C:\foo\bar.xls", Password:=p

Hi Harlan,

Thnx a lot. That is cool stuff.

Regards,
Hari
India

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:O91UjbPDFHA.2568@TK2MSFTNGP10.phx.gbl...
> Sorry, send the last one too soon.
>
> Hard-coded string:
>
> Workbooks.Open Filename:="C:\foo\bar.xls", Password:="this is hard-coded"
>
> Hard-coded ASCII codes:
>
> p = Array(116,104,105,115,32,105,115,32,104,97,114, _
> 100,45,99,111,100,101,100)
>
> w = ""
> For n = LBound(p) To UBound(p)
> w = w & Chr(p(n))
> Next n
>
> Workbooks.Open Filename:="C:\foo\bar.xls", Password:=w
>
>
> Cyphered string:
>
> Const FOOBAR As Byte = 67
>
> p = "7+*0c*0c+""1'n ,'&'"
>
> For n = 1 To Len(p)
> Mid(p, n, 1) = Chr(CByte(Asc(Mid(p, n, 1))) Xor FOOBAR)
> Next n
>
> Workbooks.Open Filename:="C:\foo\bar.xls", Password:=p
>
>