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

[Robin]

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]

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]

Hi Dave Peterson,

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

Robin.

 

[Dave Peterson]

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.

 

[Harlan Grove]

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.

 

[Robin]

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!

 

[Hari]

Hi Robin,

I don't want to apply any protection on any sheet of the file in

advance,
Not able to understand the above properly. But this is what I did when
I had a similar need. Insert a "Warning" sheet, which is a dummy
worksheet, in your real workbook. In the workbook_open macro hide the
warning sheet and unhide your actual sheets.
In Workbook_close macro hide your actual sheets and unhide the warning
sheet.
That way, if a user opens the workbook without enabling macros then
user will only be able to see warning sheet (which can have some text
similar to what Dave P has suggested) and if the user opens the
workbook by enabling macros then they will see all your actual
worksheets but not the warning sheet.

You will have to take care of hiding real sheets in workbook_close and
not workbook_save . And workbook_close should itself first check
whether workbook has been saved. If yes, then hide the sheets (and
unhide warning sheet) and then save the workbbok and close the
workbook. If no, you might give the user a msg box or see how you want
it to be handled.

Regards,
Hari
India

 

[Hari Prasadh]

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]

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)

 

[Harlan Grove]

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

 

[Hari Prasadh]

Hi Harlan,

Thnx a lot. That is cool stuff.

Regards,
Hari
India