+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    KR
    Guest

    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.



  2. #2
    Jim Rech
    Guest

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

    >>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" <nospam@nospam.com> wrote in message
    news:uC9E5BQSGHA.4600@TK2MSFTNGP11.phx.gbl...
    |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.
    |
    |



  3. #3
    Tom Ogilvy
    Guest

    RE: Intercept/replace standard 'cell protected' message with my own me

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


  4. #4
    KR
    Guest

    Re: Intercept/replace standard 'cell protected' message with my own me

    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" <TomOgilvy@discussions.microsoft.com> wrote in message
    news:F5E510C0-4B90-4E94-8E00-2A8755631A75@microsoft.com...
    > 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.
    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1