Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Customise protection message! (Excel 2000 >)

    Someone asked me recently if the message that indicates if the cell has protection, can be altered, as the default protection message is long winded and technical!
    I attempted the task and came up with this (see attachment) as a simple solution!
    As per usual, I would like to post it and ask for input and suggestions / improvements. I believe this feature can be built upon and customised to extend its capabilities!

    PS: The sheet is protected, but without the optional password! Only the formulas in the sheet are protected!

    Any thoughts or improvements welcome. The lounge is the ideal place to share and pass comments and so...
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customise protection message! (Excel 2000 >)

    I'm sorry, but I don't like that. Your message is displayed each time the user clicks in or moves to a locked cell. The built-in message is only displayed if the user tries to edit a locked cell. This message will be displayed anyway if the user tries to edit a locked cell despite your warning.

    Why don't you disallow selecting locked cells? In Excel 2002, this can be done in the Protect Sheet dialog.
    In older versions, you can set the EnableSelection property of the worksheet to 1 - xlUnlockedCells. This will have to be done in the Workbook_Open event, for the setting of EnableSelection is not stored.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customise protection message! (Excel 2000 >)

    Hans;
    You don't need to apologise! I asked for comments and input. You are just being honest!
    I do agree with your observations, and I also agree with your solution. I admit that I forgot about the EnableSelection property of the worksheet. "1 - xlUnlockedCells". This will work better as it would be less disruptive. Seeing that this is on the Open Event, a msgbox can relay the fact that locked cells would not be able to be selected.

    I did some other experimentation, and noted that the code does not function well in other events such as: BeforeDoubleClick and Change! The original protection message is not over-ridden in these.

    Anyways, I was just playing around, and if the request comes again from the person that queried this, I will develop the macro you suggested.

    Thx
    Regards,
    Rudi

Posting Permissions

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