+ Reply to Thread
Results 1 to 5 of 5

Checkboxes not working with "Allow users to edit ranges"

  1. #1
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Checkboxes not working with "Allow users to edit ranges"

    I have a range of cells with checkboxes attached to them. I have these cells locked. The workbook is shared so that multiple users can work on the sheets.
    I also used the "Allow users to edit ranges" features to give certain users the ability to edit these cells.

    If they type directly in the cell, everything is ok. If they click on the checkbox linked to the cell, it says the cell is protected and prevents the change, even though they are allowed to via the "Allow users to edit ranges" feeature.

    How can I have the checkboxes work while the sheet is protected?
    Last edited by Whizbang; 03-04-2011 at 09:55 AM.

  2. #2
    Registered User
    Join Date
    09-24-2010
    Location
    Victoria, BC
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Checkboxes not working with "Allow users to edit ranges"

    Hi Whizbang.
    First question I suppose has to be, is there a reason that the cells the checkboxes are linked to need to be locked? If not then unlocking them seems to be an easy solution.
    If they are required to be locked, is it possible for you to upload a copy of the workbook with dummmy info in it for people to work on?

  3. #3
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Checkboxes not working with "Allow users to edit ranges"

    Question 1: I was hoping to prevent people from checking sections that do not belong to them. IE, IT staff can only check IT action items. HR can only check HR items.

    Question 2: I have attached a workbook.

    The purpose of this workbook is to limit the paperwork associated with terminations of employees. When an employee is terminated, certain tasks need to be compelted by different departments. I would like to create a shared file at the time of termination that will then allow the department staff to check off each item as it is completed.

    I was hoping to use the native "Allow users to edit ranges" feature to control who can edit what. If it is not possible to use checkboxes in this manner, I will simply come up with a macro solution (ie worksheet change events that test who the user is against a list of who can edit that range), or maybe just have the users enter an "x" or something in the desired cells.
    Attached Files Attached Files

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Checkboxes not working with "Allow users to edit ranges"

    Bump!

    Has anyone had similar experiences?

    Right now I have unlocked the cells and then used the worksheet change event to test if the user is allowed to change the cell, but I would really prefer not to use the macro if I could. There is always a chance the user could disable their macros and then there would be no security.

  5. #5
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Checkboxes not working with "Allow users to edit ranges"

    I have attached my solution to the problem. It avoids the use of checkboxes altogether. I simulated the behavior and look of a checkbox using the worksheet before double click event.

    Note: The attached workbook should be protected and shared, but I have left the file open so that others may see how it works.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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