Intercept/replace standard 'cell protected' message with my own message?

I have a workbook where the users will be expected to input all data via a
userform (to ensure standardization of information), and the data that is
submitted will be added to a worksheet that has cells locked/protected.

Currently, a user can select a cell, but if they try to edit it, they get
the standard Excel "this cell is protected, if you want to change it
unprotect the sheet, and you may be asked for a password" message.

Is there a way for me to intercept whatever event is firing, and pop up my
own custom message box so I can tell the user /why/ the sheet is locked?
something more like "This sheet is protected to ensure data integrity.
Please enter all data through the userform. If you need to edit or delete
data please contact (name)"

Any help would be greatly appreciated.
Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

>>Is there a way for me to intercept whatever event is firing

I don't think so. You might use EnableSelection to limit users to selecting
unlocked cells.

--
Jim
"KR" <[email protected]> wrote in message
news:[email protected]...
|I have a workbook where the users will be expected to input all data via a
| userform (to ensure standardization of information), and the data that is
| submitted will be added to a worksheet that has cells locked/protected.
|
| Currently, a user can select a cell, but if they try to edit it, they get
| the standard Excel "this cell is protected, if you want to change it
| unprotect the sheet, and you may be asked for a password" message.
|
| Is there a way for me to intercept whatever event is firing, and pop up my
| own custom message box so I can tell the user /why/ the sheet is locked?
| something more like "This sheet is protected to ensure data integrity.
| Please enter all data through the userform. If you need to edit or delete
| data please contact (name)"
|
| Any help would be greatly appreciated.
| Thanks,
| Keith
|
| --
| The enclosed questions or comments are entirely mine and don't represent
the
| thoughts, views, or policy of my employer. Any errors or omissions are my
| own.
|
|

KR,
this question is frequently asked - and to the best of my knowledge, I have
never seen a positive answer. the answer usually given by me and others is
to prevent selection of locked cells. In xl2002/3 this can be done in the
Tools=>Protection=Worksheet protect dialog in the top two checkboxes. This
setting will be retained once set through the closing and opening of the
workbook.

It can also be set with code in all versions, but not as part of the Protect
argument. It must be done with the sheet level property EnableSelection.
See the help on this property for a list of values you can use and their
effect. This setting must be performed each time the workbook is opened, so
you would use an event to fire it when the workbook is opened, such as the
workbook_OPen event.

Chip Pearson's page on events if not familiar:
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"KR" wrote:

> I have a workbook where the users will be expected to input all data via a
> userform (to ensure standardization of information), and the data that is
> submitted will be added to a worksheet that has cells locked/protected.
>
> Currently, a user can select a cell, but if they try to edit it, they get
> the standard Excel "this cell is protected, if you want to change it
> unprotect the sheet, and you may be asked for a password" message.
>
> Is there a way for me to intercept whatever event is firing, and pop up my
> own custom message box so I can tell the user /why/ the sheet is locked?
> something more like "This sheet is protected to ensure data integrity.
> Please enter all data through the userform. If you need to edit or delete
> data please contact (name)"
>
> Any help would be greatly appreciated.
> Thanks,
> Keith
>
> --
> The enclosed questions or comments are entirely mine and don't represent the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>
>

no message is better than an uninformed one, so I've taken your advice and
disabled the selection at the worksheet level (in code, because I have to
unprotect and reprotect the worksheet every time I add data)
Thanks!
Keith

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> KR,
> this question is frequently asked - and to the best of my knowledge, I
have
> never seen a positive answer. the answer usually given by me and others
is
> to prevent selection of locked cells. In xl2002/3 this can be done in the
> Tools=>Protection=Worksheet protect dialog in the top two checkboxes.
This
> setting will be retained once set through the closing and opening of the
> workbook.
>
> It can also be set with code in all versions, but not as part of the
Protect
> argument. It must be done with the sheet level property EnableSelection.
> See the help on this property for a list of values you can use and their
> effect. This setting must be performed each time the workbook is opened,
so
> you would use an event to fire it when the workbook is opened, such as the
> workbook_OPen event.
>
> Chip Pearson's page on events if not familiar:
> http://www.cpearson.com/excel/events.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "KR" wrote:
>
> > I have a workbook where the users will be expected to input all data via
a
> > userform (to ensure standardization of information), and the data that
is
> > submitted will be added to a worksheet that has cells locked/protected.
> >
> > Currently, a user can select a cell, but if they try to edit it, they
get
> > the standard Excel "this cell is protected, if you want to change it
> > unprotect the sheet, and you may be asked for a password" message.
> >
> > Is there a way for me to intercept whatever event is firing, and pop up
my
> > own custom message box so I can tell the user /why/ the sheet is locked?
> > something more like "This sheet is protected to ensure data integrity.
> > Please enter all data through the userform. If you need to edit or
delete
> > data please contact (name)"
> >
> > Any help would be greatly appreciated.
> > Thanks,
> > Keith
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
> >