I got an Excel Macro/Security question that is driving me nuts.....

[Jon]

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........


Thank you.


Jon

 

[John Wilson]

Jon,

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute?

Actually, no there isn't so you can stop looking.

One workaround is to make all sheets but one visible when saving the workbook
(rendeiring it useless) and on that sheet place a notice that macros need
to be enabled in order to use it. If macros are enabled when it's opened,
that notice sheet will hide itself and the other sheets will be made visible.

Attached below is some code and instructions on how to do this:
(Please make note that Excel is not a secure environment so if you're
using this to protect the workbook, it can be circumvented by an
experienced user)

John

<snip>
The only way to do this is to hide all the sheets except one when
the workbook is saved (making the workbook unusable).
On that one unhidden sheet, you'll need to place some text alerting
the user that they need to enable macros and instruction on how
to do so. You'll also need to protect the VBA project so they can't
easily get at the underlying code. Please be aware though that an
experienced user can crack any VBA code that you have or make
the sheets that you've hidden, visible again even without getting
into your VBA code.

Having said that, here's some code that will attempt to do what
you want. Try this in a brandy new workbook first to see how
it works and then adapt the code to fit your needs (based on how
your sheets are laid out)

How it works:
On Close, nothing happens (except the close) because the
stored copy already has all but sheet(1) hidden.
If the user saves the workbook...
His/her last active cell address is saved in a memvar
The sub to hide all but the first sheet is activated.
By default, this activates sheet(1) firing the code there.
There's a one second timer in the sheet activation code
(allowing the actual save to take place with all but the
first sheet hidden). After one second the OpenWorkbook
sub is called taking the user to the sheet he/she was last on.
The last cell on that sheet that was active is now selected.

'***** Workbook Code*****
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Remember where the user was
WhereWasI = ActiveCell.Address
' Call sub to hide all but the first sheet
HidealmostAll
End Sub

Private Sub Workbook_Open()
' Prevent Sheet1 Activate code from firing
Application.EnableEvents = False
' Call opening ceremonies sub
OpenWorkbook
Application.EnableEvents = True
End Sub

'***** Sheet(1) Code ******
Option Explicit
Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "OpenWorkbook"
End Sub

'****Module1 Code *****
Option Explicit
Public WhereWasI As String
Sub HidealmostAll()
' Insure Sheet(1) is Visible
Sheets(1).Visible = True
Dim a As Integer
' Hide all the other sheets
For a = 2 To Sheets.Count
Sheets(a).Visible = xlVeryHidden
Next a
End Sub

Sub OpenWorkbook()
Dim a As Integer
' Display all the other sheets
For a = 2 To Sheets.Count
Sheets(a).Visible = True
Next a
Sheets(1).Visible = xlVeryHidden
If WhereWasI = "" Then Exit Sub
Range(WhereWasI).Activate
End Sub
<snip>

 

[John Wilson]

Jon,

You probably have formatted or otherwise sued some rows below or
columns to the right of your data. On the bloated workbook, select the
sheets individually and press Ctrl + End. If the last row and column
is well below where you think it should be, you'll need to delete the
empty rows and columns.

Check here for an example of how to do this both manually
and with VBA:

http://www.contextures.com/xlfaqApp.html#Unused

John

 

[John Wilson]

Tom,

The best you can do is conceal enough of your workbook

Which should be done in the Workbook.Before_Save Event.
That way it insures that (if macros were enabled when it was
opened in the first place) that it would always be saved with
the selected sheets hidden.

There is no foolproof way to do it.

Agreed

John

 

[Jon]

Thanks guys...no wonder it was driving me nuts...looks like this scenario
was not anticipated when MS added security to Office. I'm kind of surprised
though as it seems that security ought to be a 2 way street i.e. the user is
free to decline the running of code in the Excel environment but the
developer should also have a way of stopping the user from operating the
Excel spreadsheet in a way that was not intended (in the absence of macros).
I have several sophisticated Excel apps that I have developed and it bothers
me that many users may inadvertantly get themselves into trouble by
declining macros. Actually I imagine that in some situations this could
cause the applications to generate false output, and there would be no way
to tell. Not good.



Jon

PS You guys are fast too, thanks for the quick answers. I can go to bed now
without feeling like I'm missing something obvious at least!!

 

[Jon]

Haha...don't sweat it. Looks like we all get "code" disfunctionality at
night. Excle troubles or no Excel troubles! Take care...I'm going to bed.

 

[Richard Choate]

Another idea would be to put all the good stuff in a different workbook, and
then put just enough code in the "first" workbook that if they have macros
turned on, then it will open the second workbook with all of the stuff in
it. At least this way, they wouldn't have a "dead" copy which they might
tinker with. A little better control plus you don't have to do so much
hiding and stuff.
--
HTH
Richard Choate, CPA

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........


Thank you.


Jon

 

[Henry]

Jon,
Its a Catch 22 situation.
What're you gonna use to stop the user opening the workbook... a macro?
But the user has just disabled macros!!!

ATB
Henry

 

[John Wilson]

Richard,

Well, if the user doesn't know where the 2nd workbook is, or what the name
of it is, or what the PW is, then those things might keep the user out.

Whether there's one workbook or two doesn't really matter. If the user
is experienced enough to crack the VBA password (which isn't all that
difficult), I'm sure he/she would easily be able to find the name, path and
password to the second workbook within the code. Attempting to use
any home made protection scheme is nothing more than an exercise
in futility.

I'm guessing from the OP's post (though he didn't make it crystal clear)
that he had a workbook that depended on macros to operate correctly
and wanted to insure that they were enabled. I didn't get the impression
that he wanted to protect it from anyone. If that was his intent, the "hide
all sheets except one" should work rather well.

John

 

[R. Choate]

John,
All decent points. However, I think it is easier to secure a file that a
user isn't already in than one which is already open. First, the security of
a password to open a file, as I understand it, is much tougher than the
security of passwords on code or on sheets. That is, it would take a much
better expert to crack that PW. Also, the 2nd file could be placed in a
secure directory. Again, another layer. You could be clever for additional
security with the 2nd file option. Therefore, I could not agree that it is
equally futile.

However, your point about the OP not mentioning security problems is quite
valid and I am guilty of over-discussing the security issue for this post.
I'll end it here.
--
RMC,CPA


Richard,

Well, if the user doesn't know where the 2nd workbook is, or what the name
of it is, or what the PW is, then those things might keep the user out.

Whether there's one workbook or two doesn't really matter. If the user
is experienced enough to crack the VBA password (which isn't all that
difficult), I'm sure he/she would easily be able to find the name, path and
password to the second workbook within the code. Attempting to use
any home made protection scheme is nothing more than an exercise
in futility.

I'm guessing from the OP's post (though he didn't make it crystal clear)
that he had a workbook that depended on macros to operate correctly
and wanted to insure that they were enabled. I didn't get the impression
that he wanted to protect it from anyone. If that was his intent, the "hide
all sheets except one" should work rather well.

John

 

[John Wilson]

Richard,

the security of a password to open a file, as I understand it,
is much tougher than the security of passwords on code or on sheets.

Very true. Most of the password crackers that you can buy to
break that password use brute force and can take a few days to
accomplish their task depending on the password.

That is, it would take a much better expert to crack that PW.

That it would ($45.00 to www.lostpasswords.com)

Also, the 2nd file could be placed in a secure directory.

In your scenario though, that second file is opened via
VBA from the first file. In order to do that, the password and
any necessary code to get to that file and open it would have
to be in the VBA coding of the first file. Least common denominator
is the VBA in the first file. Once that's cracked and you can see the
code, the rest is child's play.

Therefore, I could not agree that it is equally futile.

The extent of futility depends on the knowledge and experience
of the user that you're trying to protect it from. Granted, there are
many who don't even know what a macro is and for them, protecting
a workbook and VBA code would probably work well. I'm
certain that most, if not all, of the regular contributors to the ng's
would have no problem at all with whatever protection scheme that
you or I or anyone else could come with.

The point of all of this is that while protecting a workbook or the
VBA coding is not secure, many people still do ask for ways to
do it. Whenever I answer one of these posts, I always make it a
point to let the OP know how vulnerable it is. If they still decide
to use whatever I give them, it's their choice. To give the OP
the impression that there *is* a way within Excel to make their
work safe and secure from prying eyes would be to mislead them.

John

 

[R. Choate]

John, good points as always. Can't really argue with most of them and I
enjoyed the discussion. I also look forward to meeting with you on other
posts. Thanks for your time and knowledge.
--
RMC,CPA


Richard,

the security of a password to open a file, as I understand it,
is much tougher than the security of passwords on code or on sheets.

Very true. Most of the password crackers that you can buy to
break that password use brute force and can take a few days to
accomplish their task depending on the password.

That is, it would take a much better expert to crack that PW.

That it would ($45.00 to www.lostpasswords.com)

Also, the 2nd file could be placed in a secure directory.

In your scenario though, that second file is opened via
VBA from the first file. In order to do that, the password and
any necessary code to get to that file and open it would have
to be in the VBA coding of the first file. Least common denominator
is the VBA in the first file. Once that's cracked and you can see the
code, the rest is child's play.

Therefore, I could not agree that it is equally futile.

The extent of futility depends on the knowledge and experience
of the user that you're trying to protect it from. Granted, there are
many who don't even know what a macro is and for them, protecting
a workbook and VBA code would probably work well. I'm
certain that most, if not all, of the regular contributors to the ng's
would have no problem at all with whatever protection scheme that
you or I or anyone else could come with.

The point of all of this is that while protecting a workbook or the
VBA coding is not secure, many people still do ask for ways to
do it. Whenever I answer one of these posts, I always make it a
point to let the OP know how vulnerable it is. If they still decide
to use whatever I give them, it's their choice. To give the OP
the impression that there *is* a way within Excel to make their
work safe and secure from prying eyes would be to mislead them.

John

 

[Jon Furman]

Wow..didn't know I was going to start of such a discourse here. I do
appreciate all of your replies and discussion on this issue. I'm
implementing the all sheets hidden except one solution. As one of you all
pointed out, that's really all I need. I'd prefer not to spend too much time
trying to make xls's completely secure since I'm of the school of thought
that there is nothing that is completely secure and that if someone wants to
get in, eventually they will. All I really want to do is keep the end users
from shooting themselves in the foot by declining the macros. I would like
to see a feature in Excel where the workbook won't open if the users decline
macros, so if anyone from MS is reading this would be a very useful addition
to Office security. Once again, thanks so much for all the good discussion!


Jon

 

[R. Choate]

Yeah, John and I had a little fun with it and debated a tiny bit. John
suggested you just use the hidden sheets solution, which I would generally
agree with. Other ideas are OK for discussion, but for the most part, John
is correct when he tells you there is no great security in Excel so the
hidden sheets are as good as any. Best of luck to you.
Richard
--
RMC,CPA


Wow..didn't know I was going to start of such a discourse here. I do
appreciate all of your replies and discussion on this issue. I'm
implementing the all sheets hidden except one solution. As one of you all
pointed out, that's really all I need. I'd prefer not to spend too much time
trying to make xls's completely secure since I'm of the school of thought
that there is nothing that is completely secure and that if someone wants to
get in, eventually they will. All I really want to do is keep the end users
from shooting themselves in the foot by declining the macros. I would like
to see a feature in Excel where the workbook won't open if the users decline
macros, so if anyone from MS is reading this would be a very useful addition
to Office security. Once again, thanks so much for all the good discussion!


Jon