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?

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.


On Apr 27, 4:16*pm, Schatzi wrote:
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?

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.

On Apr 27, 11:00*am, AB wrote:
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.

On Apr 27, 4:16*pm, Schatzi wrote:



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?- Hide quoted text -

- Show quoted text -

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.

On Apr 27, 11:00 am, AB wrote:
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.

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

On Wed, 27 Apr 2011 09:13:01 -0700 (PDT), Schatzi
wrote:

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.

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/micro...e32517a28654b0
but do not understand how it is implemented. I do not see which add-in
they are referring to.
Thank you for your replies.

On Apr 27, 1:36*pm, Gord Dibben wrote:
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

On Wed, 27 Apr 2011 09:13:01 -0700 (PDT), Schatzi
wrote:



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.- Hide quoted text -

- Show quoted text -

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Wed, 27 Apr 2011 09:13:01 -0700 (PDT), Schatzi
wrote:

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.

On Apr 30, 6:50*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet. org wrote:
On Wed, 27 Apr 2011 09:13:01 -0700 (PDT), Schatzi

wrote:
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.

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.