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

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

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

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.

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!

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

I have discovered a big potential problem with this approach after trying to implement it myself.

Lets say a user has macros enabled and goes into the workbook. they then make some changes and save the workbook (the book is now saved with all sheets visible). then they make some more changes but this time screwed something up and want to exit the file without saving. If you allow them to exit the file without saving, the file is saved with all of the sheets in full view except for the dummy warning sheet. Then lets say another user comes along who has macros disabled, he gets straight into the file with all sheets visible and macros are still disabled.

I cant see any way around this other than use the 2 file approach and have one file open the other, then they can do whatever they like to their file. Unfortunately this approach is very messy and very hard to implement reliably, I would much rather have everything contained within the one file but I cant see any way around the problem above. Anyone else have another solution which involves only 1 file?

Originally Posted by 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