security, enabling macros to enable a disabling saving function

[Schatzi]

I am publishing the workbook in a shared file and would like users to
not ahve to always enable macros themselves. The problem is security.
I want several users to be able to access the workbook, but not be
able to save it. I locked up the workbook and disabled the save
buttons (I put code in the "thisworkbook" file in VB. The disabling of
the save function only works when macros are enabled. Thus, the user
would be able to save it before enabling macros and running the
workbook. Once they have it on their personal computer they would be
able to hack into it as they please. Any ideas?

 

[AB]

The security setting is dependant on the local machine and one can't
override it as far as i know.
What i tend to do in cases like these - make the wb unusable without
macros enabled, e.g., hid (very hidden) all but 1 ws, pwd protect the
vbe project and then have the thisworkbook_open code unhide the
necessary ws. So, if the user doesn't enable macros - the wb is
useless.

 

[Schatzi]

That is a great idea. How do I use macros to unhide a workbook?
This way, if they initially save a copy to their own drive, it will be
more difficult for them to hack it, but not impossible. Hopefully they
will be less likely to try and save it immediately when they see that
nothing is available.

 

[GS]

Schatzi used his keyboard to write :

That is a great idea. How do I use macros to unhide a workbook?
This way, if they initially save a copy to their own drive, it will be
more difficult for them to hack it, but not impossible. Hopefully they
will be less likely to try and save it immediately when they see that
nothing is available.

AB isn't suggesting you hide the workbook, only all worksheets except
one. Put code in either an Auto_Open sub in a standard module
(preferred) OR the Workbook_Open event behind ThisWorkbook (not
recommended).

The visible sheet could be a dummy sheet with text to inform users they
must enable macros when the workbook opens.

When macros are enabled, the Auto_Open sub should first unhide the
hidden sheets, then hide the dummy sheet, then disable your menus and
makes any other UI changes you desire. All this happens behind the
scene because the first line of code should be...

Application.ScreenUpdating = False

Since you'll need to restore things at closing, use an Auto_Close sub
(in the same module as Auto_Open) to hide/unhide sheets appropriately
and restore disabled menus and any other UI changes your project made.

Make the project in the earliest version of Excel you expect users will
have running on their machine. (It will still work in the later
versions no problem)

Note:
The reason for not recommending putting Open/Close code behind
ThisWorkbook is because if, for any reason, the workbook becomes
corrupt the code will not work as expected. Using Auto_Open/Auto_Close
in a standard module has proven to be a more reliable approach<IMO>.

HTH

 

[Gord Dibben]

You don't hide the workbook..........you hide all sheets but a dummy sheet that
has the message in 36 pt. font

"By disabling macros you have rendered this workbook unusable. Please close and
re-open with macros enabled."

Sample code in Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

If users enable macros then your "no save" code will run as desired.

Make sure you protect the project from viewing and set a good password on that
protection.

It is more difficult for users to crack VBA project passwords.


Gord Dibben MS Excel MVP

 

[Schatzi]

I meant unhide the worksheets, not workbook .

I am working on the code. How do I make it so that the worksheets
cannot be unhidden by the user. I would like them not even to show up
so that the user does not know they are there. I saw this discussion:
http://groups.google.com/group/micr...=gst&q=unhide+worksheet+stop#b2e32517a28654b0
but do not understand how it is implemented. I do not see which add-in
they are referring to.
Thank you for your replies.

 

[GS]

Schatzi formulated the question :

I am working on the code. How do I make it so that the worksheets
cannot be unhidden by the user. I would like them not even to show up
so that the user does not know they are there.

Sheets("Sheet1").Visible = xlSheetVeryHidden

OR

In the VBE Properties Window set this there manually.

 

[Schatzi]

Where would I put the code so that it is continually being
implemented?:
Sheets("Sheet1").Visible = xlSheetVeryHidden

 

[GS]

Schatzi brought next idea :

Where would I put the code so that it is continually being
implemented?:

In the Workbook_Open event behind ThisWorkbook. See Gord's code example
for the Workbook_BeforeClose event. It unhides the 'dummy' sheet and
hides all the others. In the Workbook_Open event you want to unhide all
the others and hide the 'dummy' sheet. Pay attention to the order
things are done; unhide 1st, hide 2nd.

In the VBE select Workbook from the left hand dropdown of the code
window. This will insert the Workbook_Open sub where you put the code
to unhide all your sheets and hide the 'dummy' sheet.

In the right hand dropdown select 'BeforeClose' to insert the sub Gord
provided code for. Copy Gord's code into that sub. Save the project.

Close and reopen the file with macros disabled. You should only see
your dummy sheet!

Close and reopen the file with macros enabled. You should see all
sheets except the dummy sheet!

 

[Gord Dibben]

Schatzi

Go back to the response I gave a couple days ago.

There is code placed in Thisworkbook module that makes all sheets xlveryhidden.

Except for the "Dummy" sheet.


Gord Dibben MS Excel MVP

 

[Schatzi]

I placed the code in the thisworkbook. I took out the save disabling
function and closed it. Then when I re-opened it I restored the save
disabling macro.

Everything is now running perfectly.

Thank you so much for your help. I have barely begun learning VB code,
so thanks for being patient and very explanative in your responses.

 

[CellShocked]

That is a great idea. How do I use macros to unhide a workbook?
This way, if they initially save a copy to their own drive, it will be
more difficult for them to hack it, but not impossible. Hopefully they
will be less likely to try and save it immediately when they see that
nothing is available.

If you have workers that sit and hack spreadsheets instead of what they
are supposed to be doing, and you do not notice that they did not get
what they were supposed to get done DONE, then you either do not assign
them enough work, or do not hold them to the timelines of the work you
give them, or do not police them well enough to insure that they are
working on said assignments, instead of attempting to hack company
spreadsheets.

I would simply make a policy that states that if they are modifying
workbooks specifically meant to be used as is, they are committing acts
other than their JOB DUTIES and are subject to disciplinary action.

If work assignment levels are so lax that they have enough time to
perform such illicit hacking behaviors, then again, it is YOU that is not
giving them enough assignments to be sure they stay busy.

No work? Have them polish up their duties, or think of things yourself
for them to polish up to improve operations or the company image in some
way.

It shouldn't be hard to weed out the freeloaders and those with ill
intent.

 

[Schatzi]

  If you have workers that sit and hack spreadsheets instead of what they
are supposed to be doing, and you do not notice that they did not get
what they were supposed to get done DONE, then you either do not assign
them enough work, or do not hold them to the timelines of the work you
give them, or do not police them well enough to insure that they are
working on said assignments, instead of attempting to hack company
spreadsheets.

  I would simply make a policy that states that if they are modifying
workbooks specifically meant to be used as is, they are committing acts
other than their JOB DUTIES and are subject to disciplinary action.

  If work assignment levels are so lax that they have enough time to
perform such illicit hacking behaviors, then again, it is YOU that is not
giving them enough assignments to be sure they stay busy.

  No work?  Have them polish up their duties, or think of things yourself
for them to polish up to improve operations or the company image in some
way.

  It shouldn't be hard to weed out the freeloaders and those with ill
intent.

I am not worried in quite the way you are thinking. First, the workers
are not directly under me. Second, the concern is that they might
leave the company and take the program with them. While part of the
company, they can use it to their heart's content, so there would be
no need to hack the program. If they took the program with them, then
there would be an issue.