Workbook protection help

  • Thread starter Thread starter john_the_mackem
  • Start date Start date
J

john_the_mackem

Recently one of my end-users has discovered a macro which allows th
workbook protection password to be removed. This means she now has ful
read-write access to the spreadsheet.

Is there anyway for me to regain control? I can't use macros becaus
they can be disabled. She needs read access but I want to prevent he
from editing cell values or seeing formulas.

I am using Excel 2002. Thanks for any help
 
Maybe you could get into a war of protection with her.

For the worksheet that needs to be protected:

Rightclick on the worksheet tab and select view code. Then paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Protect Password:="hi"
End Sub

If she enables macros, then everytime she changes the selection, it'll reapply
the protection.

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.
 
Thanks for the reply Dave, much appreciated. I will give it try.

Although there is one thing... if there are any clever VB programmer
out there.... how can I do these things programmatically?
It's difficult to describe how it works, but the Excel spreadsheet m
report is based on is a globally-visible template (which I can'
change), and my report is automatically generated and emailed to th
end-users when business conditions are met. This means for Dave'
suggestions to work, I would have to introduces them at design-tim
through code.
I use the Excel 10.0 Object library in Visual Basic to generate th
report.

Thanks,
John
 
You might be able to use a class module.

See Chip Pearson's site for a nice explanation:
http://www.cpearson.com/excel/AppEvent.htm

But if the user were smart enough to unprotect your worksheets, then I bet that
user is smart enough to not load your addin that would do the monitoring of
protection.
 
Back
Top