Intercept protection message

[Guest]

I would like to intercept the worksheet or workbook protection message and
run code to handle someone trying to change a protected worksheet. Is this
possible? Thanks

 

[Vasant Nanavati]

Unfortunately, I don't believe this is possible.

 

[Tushar Mehta]

The idea behind setting protection parameters is to let XL do the work
of protecting the workbook/sheet. If you'd rather do the work yourself,
turn off protection and trap the various Selection and/or
SelectionChange events. Do keep in mind that not all things that can be
changed trigger events. One example is changing the name of a
worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Guest]

All worksheets are protected unless VBA code is operating. Movement between
sheets is controlled and functionality of sheets varies and is controlled. In
all but one case user presented with protection message is OK. But in one
case I would prefer to present my own message/promt. I’ve worked around the
problem but the work around solution reveals the amount of control that the
program has. I would prefer the user feel at ease with using the worksheets
so that the original feel of a spreadsheet 20 years ago is still at play thus
taking advantage of creative minds and solutions.

 

[Dave Peterson]

Maybe you could just stop the user from selecting a locked cell.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With
End Sub

Excel won't remember these settings after you close it and reopen the workbook
(that's why it's in auto_open).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I'm not sure what message you're seeing from the workbook protection, though.