+ Reply to Thread
Results 1 to 8 of 8

Help: Macro NOT Working After Protecting Sheet

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    31

    Question Help: Macro NOT Working After Protecting Sheet

    Hello,

    I have a workbook that I don't want the users to be able to change anything of the format, columns, rows, etc. so I protected the worksheet.
    I have a macro saved in the sheet itself and not as a module that would allow users to select more than one option in the data validation drop-down lists. When I protect the worksheet I am not able to select more than one entry from the drop-down list. Hence, the macro is not working.

    See attached workbook for more information

    My question is: How can I protect the worksheet and at the same time have the macro work so I can select more than one option.

    thanks for your help

    below is the code that allows multiple selection

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help: Macro NOT Working After Protecting Sheet

    To have a macro work on a protected sheet, you typically have to unprotect the sheet, do your actions on the sheet, then protect again.

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 01-06-2020 at 11:41 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-09-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Help: Macro NOT Working After Protecting Sheet

    I know what you're saying but that does not work.
    I need to have the macro running while the sheet is protected.
    If I add those lines to the code it still will not allow the user to select more than one entry from the drop-down list.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help: Macro NOT Working After Protecting Sheet

    When you protect a sheet, you should have UNLOCKED the cells you wish users to interact with and or macros to run freely on. Select those cells, CTRL-1, Protection > [x] Locked.... uncheck that. Then protect your sheet and those cells remain editable, if you protect the sheet correctly.

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Help: Macro NOT Working After Protecting Sheet

    JB, the issue is actually that when a worksheet is protected, you cannot select the cells with Data Validation using SpecialCells (you can't do it manually using F5 > Special, either). Also, those cells are already unlocked in their Properties. I'd suggest updating your code to:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help: Macro NOT Working After Protecting Sheet

    @Paul - good to know. I'd never run into that before, and that is something I would have done. Thanks.

  7. #7
    Registered User
    Join Date
    03-09-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Help: Macro NOT Working After Protecting Sheet

    Great!!!! Thanks Paul!!! It worked

  8. #8
    Registered User
    Join Date
    01-06-2020
    Location
    Patras, Greece
    MS-Off Ver
    2016
    Posts
    1

    Re: Help: Macro NOT Working After Protecting Sheet

    Thank you very much Paul, even so many years after your code worked just fine!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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