+ Reply to Thread
Results 1 to 7 of 7

Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

  1. #1
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    I have three column ranges on a sheet, each with list-based data validation. In order to change the zoom of 61 to a more readable 100 for purposes of choosing a selection from a validation drop-down, I have the following code in the sheet module. When the sheet is unprotected, toggling the zoom works perfectly. When the sheet is protected and a validation code double-clicked, the sheet does not unprotect and the code hangs until ESC is pressed. There is not password. I am stymied. Any hints or solutions will be greatly appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    After the first End If statement I think you would protect the sheet again
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    Thank you YasserKhalil. I took out ActiveSheet.Protect during testing. Will add line if I can get the Unprotect code working. I am going to set up a similar scenario in a new workbook to see if the code works there.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    Excel Data Validation stops VBA code from running until selection is committed to the cell (code continues after).

    I suspect protection stops validation from committing value to the cell and code hangs.

    Try putting the code in standard module and assigning it to a short cut key and test if that works.

  5. #5
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    CK76, Thank you for your response and your insight! Intuitively, it made sense. To test I tried your suggestion to put code into a standard module but could not figure a way to assign a short cut key or to otherwise run code. Appears it must be run from a sheet module. I was able to test another way. To rule out any other possible conflicts in this complex workbook, I set up a new workbook and recreated a similar scenario with three columns, each with validation. The problem was replicated. I then removed validation on one column, uncommented the ActiveSheet.Protect code noted by YasserKhalil and double-click code runs perfectly when sheet is protected. So you are correct. Validation stops VBA code from running until selection is committed to the cell. NOW, is there a way to overcome this problem?

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    One way to do it. Put below in standard module.
    Please Login or Register  to view this content.
    Then assign it a short cut key. You can run code without double click via key combination.

    Or assign it to "SelectionChange" event, instead of "BeforeDoubleClick".

    See if one of the method works for you.

  7. #7
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Cannot Unprotect Sheet in a Worksheet_BeforeDoubleClick Macro

    CK76,

    Your help was spot-on and of great value to me!

    See completed code below.


    Please Login or Register  to view this content.
    NOTE: The above code will not work on a protected sheet, nor will the sheet unprotect, unless, when the sheet is protected, "DrawingObjects:=False", without the quotes.
    Last edited by mandora; 03-06-2017 at 02:16 PM.

+ 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. [SOLVED] Macro to Unprotect Sheet, Paste then Protect Sheet
    By tokyojo1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-09-2014, 02:02 PM
  2. [SOLVED] Worksheet_BeforeDoubleClick with sheet protection works every other time
    By Jovica in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2013, 05:01 AM
  3. Macro to unprotect sheet - run macros - then unprotect sheet is not working
    By AnnieBrownTX in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 06:59 PM
  4. Macro to Unprotect Sheet, Sort then Protect Sheet
    By lisamckinney in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2010, 05:23 PM
  5. Using a macro to unprotect a sheet
    By adr150 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-25-2010, 06:05 PM
  6. Macro to Unprotect sheet!?
    By Neo1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2006, 12:50 PM
  7. [SOLVED] Unprotect and Protect sheet within macro
    By John F in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2005, 01:06 PM

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