+ Reply to Thread
Results 1 to 5 of 5

Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" option ?

  1. #1
    Registered User
    Join Date
    08-18-2015
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    6

    Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" option ?

    Hello everybody,
    This is my 1st post, so I hope I can make it clear.

    I have a Workbook which contains of 15 Sheets. Each Sheets consists of:
    1. Formulas. {Which I don't want anyone to mess with them}
    2. Ranges "Group 1" in every Sheet. {Which I need ONLY Team 1 to be able to edit them}
    3. Ranges "Group 2" in every Sheet. {Which I need ONLY Team 2 to be able to edit them}

    The easy part for me was:
    1. Assign a Macro to Protect all Sheets and linked it to a custom icon in the Ribbon. {Using "CustomUIEditor"} To be able to Protect all Sheets by 1-Click. {Password "ABC"}
    2. Assign a Macro to Unprotect all Sheets and linked it to a custom icon in the Ribbon. {Using "CustomUIEditor"} To be able to Unprotect all Sheets by 1-Click.
    3. Setting "Allow Users to Edit Ranges" with Password "1" for Team 1 in every Sheet.
    4. Setting "Allow Users to Edit Ranges" with Password "2" for Team 2 in every Sheet.

    What I need is to be able to run macros to:
    1. Unlock all the Ranges "Group 1"
    2. Unlock all the Ranges "Group2"

    So, my question is:
    Is there is a code to unlock those cells within the "Allow Users to Edit Ranges" ?
    {Sorry, for the long explanation, just needed to be sure it's clear. Thanks in advance.}

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" opti

    Hi i7ooo and welcome to the forum,

    Sorry you got the message to start your own thread.

    If you have a workbook that has several sheets and different groups are to work on one sheet only, I'd split the sheets up into different workbooks. That way each group would only get a single sheet and have no ability to see the other sheets. Then you collect all the workbooks from the different groups and merge or deal with them together using the Data Tab in Excel.

    See
    https://support.office.com/en-us/art...d-d22a2eea2d46
    or
    https://msdn.microsoft.com/en-us/lib...ffice.14).aspx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    08-18-2015
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    6

    Re: Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" opti

    Test Sheet.xlsmHi MarvinP, thanks for your kind response.

    For your answer, that would be great IF they work on separate sheets, but unfortunately both team works on all sheets but different columns.
    I've attached a part of file to reduce it's size, but it won't matter anyway.
    As you'll see, there are 9 sheets which are currently manged as following:
    1. All locked (Password "szz")
    2. All unlocked
    The reason is that there is 2 companies working on this File. The 1st one {Black values} propose it's desired percentage and the 2nd one {Red Values} reply with it's approved values.
    For now, the 1st company isn't allowed to modify anything rather their {Black values}, while the 2nd one -my team- are allowed to edit everything in the file including formulas.
    As you can see I've created a new Tab in the Ribbon {Aboul Zahab} to automatically lock and unlock all the sheets at once. The reason I did that because this File is used periodically a lot and it's a time consuming to do this manually every-time for the 15 Sheets.

    What I really need is a 2nd level of protection which allows My Team to work on their values without messing with the formulas, which I thought that using "Allow Users to Edit Ranges" option would help, but unfortunately I couldn't know how to run it from a macro.
    Sorry for the long explanation, but I got really desperate about this issue, hope you have the time to read it, thanks again

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" opti

    My only guess to how you might do this is with some VBA and Events of "SelectionChange". I'm not sure how you assign passwords but some programming might work. Can each team/group see all the data in the workbook? If a group clicks on a column they aren't allowed to edit you can throw up a message and move them back on their own column.

    Is that something that might work for you?

  5. #5
    Registered User
    Join Date
    08-18-2015
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    6

    Re: Macro to unprotect Ranges which are protected by the "Allow Users to Edit Ranges" opti

    Yeah, they are allowed to see all the data, just the editing is not allowed in each-other columns, and of course -my main purpose- no one can edit the formulas.
    Yes, as you said, my main problem is how to assign the password in vba.
    And your solution may be so helpful, but still, how can I assign who edit what ?
    I need something like:
    1. me -> edit everything [unlocked sheet, already done]
    2. Black Team -> edit only unlocked cells [Blacks values, already done]
    3. Red Team -> edit everything "except" formulas [if that is easier than editing only Red Values]

    any ideas ?

+ 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. Automate "Allow Users to Edit Ranges"
    By Don Rouse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2023, 05:11 AM
  2. Replies: 0
    Last Post: 04-07-2014, 10:01 AM
  3. [SOLVED] Allow users to edit ranges not working after running a Macro?!
    By Margate in forum Excel General
    Replies: 2
    Last Post: 02-15-2013, 07:42 AM
  4. Replies: 4
    Last Post: 03-04-2011, 09:55 AM
  5. [SOLVED] Add Permission did not work in "Allow User to Edit Ranges"
    By hsinlin58 in forum Excel General
    Replies: 0
    Last Post: 05-22-2006, 10:15 AM
  6. Stop users from accessing "Protection" option from "Tools" menu
    By I Believe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2005, 10:50 AM
  7. [SOLVED] Automate "Allow Users to Edit Ranges"
    By Don Rouse in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2005, 06:20 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