Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Disable notification when user clicks locked cell

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Disable notification when user clicks locked cell

    Hello,

    i've got the following problem:

    I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.

    When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
    After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.

    How can I prevent this message from popping up?

    I've already tried
    Code:
    application.displaywarnings = false
    but that didn't work

    Thanks

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,348
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Unlock the cells, so the message doesn't appear.
    Set Validation on those cells to the formula =FALSE, so the user can't change the values.

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Try this

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    '
    'code
    '
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Dec 2009
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    really that simple....

    This did the trick, Thanks!

    What exactly does the cancel parameter do in this example? Does it cancel the popup or some kind of other action?

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Cancel prevents the default action of double clicking which is to put the cell in edit mode.
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    Apr 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Hi VoG,
    As much i can get i need to paste it for individuall worksheet......
    Is there some way that i can apply this for whole workbook...?

  7. #7
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    In the ThisWorkbook module

    Code:
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    '
    'code
    '
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    New Member
    Join Date
    Apr 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Thanks VoG.....

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    Greetings, VoG,

    I have a similar question but in my case, I don't double-click! I try to enter a single character in a locked cell without getting the notification. How can I suppress the notification when entering a character in a locked cell (with a single click)?

  10. #10
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Disable notification when user clicks locked cell

    I'm not sure that you can but please start a new thread for this instead of tagging on to a rather old thread.
    HTH, Peter
    Please test any code on a copy of your workbook.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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