+ Reply to Thread
Results 1 to 5 of 5

Double click event in protected sheet to trigger color change and value insertion

  1. #1
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Double click event in protected sheet to trigger color change and value insertion

    Hey crew,

    Need your help. I have this worksheet with some code in it already. Kindly refer to it.

    The objective is that any time a cell in the ranges I have defined is double clicked, the cell that was double clicked will turn blue, and the cell in the the corresponding row in column D returns the score rating in target column row 8.

    Example, if I double click on H13. This cell (H13) will turn blue, all other cells in range E11:L14 will clear color fill formatting, and D10 will return "4" (because column H row 8 has a 4). Similarly, if I double click on I17, I17 will turn blue, all others in E17:L18 will clear color, and D17 will return a "5" (I8).

    Sheet1 is protected. Passwords are stored in worksheet pwdsheet which is xlveryhidden.

    The problem is that if Sheet1 is unprotected the code works. Once it runs the first time, the sheet will protect itself and double clicking will not work. It doesn't work if I close and reopen either. I'm an amateur coder, so can you please clean up my code?

    Thanks

    Ron
    Attached Files Attached Files
    Last edited by ron2k_1; 08-04-2017 at 08:38 PM. Reason: clarity
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,069

    Re: Double click event in protected sheet to trigger color change and value insertion

    Try this...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Double click event in protected sheet to trigger color change and value insertion

    Hi Sir,

    Still not working

    Nothing happens when I double click. It works if Sheet1 is unlocked, but after that the sheet locks itself with the password in worksheet "pwdsheet B2" and nothing happens when I double click.

    Ron

  4. #4
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Double click event in protected sheet to trigger color change and value insertion

    I had to add .EnableSelection property to the lock. It's working now. Did I do it right?

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,069

    Re: Double click event in protected sheet to trigger color change and value insertion

    Glad you figured it out.

    You don't need the first EnableSelection when it unprotects the sheet. It doesn't hurt having it though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 07-29-2017, 09:59 AM
  2. Double click to change cell color
    By amein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2016, 03:12 AM
  3. [SOLVED] Double click checks a cells color in another sheet
    By hhpack3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 02:06 PM
  4. [SOLVED] Before double click event with if statements (Double clicking blank cell stops bdc firing)
    By camdenpars in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-27-2014, 09:03 AM
  5. [SOLVED] Double Click Event to change and limit other cells
    By Bonnister in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2013, 05:03 AM
  6. Double Click to Source Data in a protected sheet
    By B16NKO in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2010, 09:44 AM
  7. [SOLVED] How to trigger code in Excel add-in after double-click or right-cl
    By Ferko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2006, 03:20 AM

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