+ Reply to Thread
Results 1 to 4 of 4

VBA Code Stops Working when Protect Sheet Enabled

  1. #1
    Registered User
    Join Date
    03-23-2021
    Location
    New York
    MS-Off Ver
    13127
    Posts
    2

    VBA Code Stops Working when Protect Sheet Enabled

    My original spreadsheet had two columns that imported values based on VLookup functions. Both columns were locked for editing and protected to prevent users from changing data in those two columns. This works fine and as intended. I now need to add new columns that can contain one or more values, so I wrote some code to allow multiple entries for cells in the new column. That works too. However, when I protect the sheet, the locked columns work like before, but the multiple selections for the new column stops working. When I unprotect the sheet, everything works but I really need to protect the two original cells. I tried adding subs for unprotect and protect and call them before and after the script, but I get a compile error, Member already exists in an object module from which this object module derives. I am trying to solve this on my own and watched countless videos to no avail. I am hoping someone can point me in the right direction and either tell me what I am doing wrong and why, and what I need to research to resolve. Any help is greatly appreciated.

    Original Code - Works fine when the sheet is unprotected.

    Please Login or Register  to view this content.
    Same Code using Protect/Unprotect Calls - This gives me the Compile error on Unprotect

    Please Login or Register  to view this content.
    This works based on guidance received.

    Please Login or Register  to view this content.
    Last edited by NJA3; 03-24-2021 at 11:09 AM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,742

    Re: VBA Code Stops Working when Protect Sheet Enabled

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please wrap code into CODE tag. As above.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    03-23-2021
    Location
    New York
    MS-Off Ver
    13127
    Posts
    2

    Re: VBA Code Stops Working when Protect Sheet Enabled

    Awesome, your suggestion worked. Original cells are protected and multiple values are captured on the new columns.

    Here is the code:

    Please Login or Register  to view this content.

    I hope I did this right. Thread is marked resolved, kudos was offered to the person who helped me, and now I am wrapping my code in the tags. Please let me knowif I did something wrong, this is new to me and I had a difficult time finding examples.

    Warmest Regards,
    Nick
    Last edited by NJA3; 03-24-2021 at 11:13 AM.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,742

    Re: VBA Code Stops Working when Protect Sheet Enabled

    Happy to help.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Please edit your original post and wrap code into CODE tag.

+ 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. Code stops working when count is zero
    By little_angel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2019, 07:47 AM
  2. [SOLVED] Code stops working
    By Excelski in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2017, 12:23 PM
  3. Code stops working until I reboot
    By AndrewMac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2013, 07:46 PM
  4. Removing code line stops code working?!?!?
    By adamj1910 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2012, 02:36 PM
  5. [SOLVED] code stops working after X rows.
    By colwyn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2008, 03:14 PM
  6. VBA code not working when I protect the sheet
    By ss_bb_24 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2008, 04:47 AM
  7. VB Code stops working
    By Michelle K in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-28-2005, 05:06 PM

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