+ Reply to Thread
Results 1 to 3 of 3

Edit specific objects in protected work sheet

  1. #1
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    126

    Edit specific objects in protected work sheet

    Hi All,

    I've created a protected Excel 2010 staff scheduling work sheet that contains multiple form control buttons and one ActiveX combo box named "TempCombo". The combo box is part of an auto complete data validation list macro that I sourced from here:

    http://www.contextures.com/xlDataVal11.html

    This code is activated when users double click in an unprotected cell that has a data validation list. The code opens the TempCombo combo box in the target cell allowing auto completion of the validation list as they type. A great solution that is integral to the functionality of the sheet.

    There is one caveat however in the use of this code in a protected worksheet - If the worksheet is protected you must allow users to Edit Objects to enable use of the combo box. The problem this creates is that it also allows all the form control buttons to be edited which defeats my reason for requiring a protected work sheet. The sheet originally used all ActiveX control (which had this issue beaten) but some bugs surfaced that meant I had to switch to form control. I started a thread about the issue on Excel Forum here:

    1055146-command-button-text-enlarges-when-print-preview-is-exited

    The only two solutions I can come up with are:

    1) To access the ActiveX combo box from a hidden, unprotected sheet. I'm not really sure how I would rework the Contextures code to do this but it sounds feasible.
    2) Protect the work sheet and only allow specific objects to be edited. i.e. the ActiveX combo box. Not sure if this is possible?

    Users must not be able to edit the form controls in this work sheet. Any ideas would be greatly appreciated!

    Many thanks,
    Last edited by L plates; 12-13-2014 at 02:33 PM. Reason: Adjust title to be more specific
    Dave C

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Edit specific objects in protected work sheet

    Hi Dave,

    With the following code I was able to use both 'Active X' and 'Forms' ComboBoxes and CommandButtons (Excel 2003). I was also successful with 'Active X' TextBoxes. If the ComboBoxes (TextBoxes) were linked to a cell, the cell had to be unprotected. This should not be a problem, because the linked cell can be placed beneath a control.

    Please Login or Register  to view this content.
    Lewis

  3. #3
    Registered User
    Join Date
    05-19-2020
    Location
    Melbourne
    MS-Off Ver
    365
    Posts
    1

    Re: Edit specific objects in protected work sheet

    Hi Sorry to bring this back from the past, how would i implement the code to the "TempoCombo" vb code so it can still be used when the sheet is protected?

    cheers

    Kev


    Apologies, managed to find an answer to it. as per below instructions.

    Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

    cheers

    Kev
    Last edited by yakamo; 05-21-2020 at 04:45 AM. Reason: found a solution.

+ 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. Protect sheet and edit Objects
    By FrankD10 in forum Excel General
    Replies: 3
    Last Post: 08-06-2012, 04:02 PM
  2. Protected Sheet Allow Edit Objects
    By jonhfl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2011, 10:38 AM
  3. Code to Protect Sheet Allowing Format Rows & Edit Objects
    By Kristine T. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2009, 11:47 PM
  4. Protect Sheet Allowing Users to Format Rows/Edit Objects?
    By Kristine T. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2009, 04:38 PM
  5. Protect Sheet>Edit Objects except textbox
    By tianah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2009, 12:28 AM

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