+ Reply to Thread
Results 1 to 4 of 4

Allowing sort on protected sheet without edit range or macros so can use in Excel Online

  1. #1
    Registered User
    Join Date
    06-24-2020
    Location
    Market Harborough, England
    MS-Off Ver
    Excel 365
    Posts
    16

    Allowing sort on protected sheet without edit range or macros so can use in Excel Online

    Hi.

    I've done a bit of research on the title problem and think that there is no solution so i'm after confirmation of this or a clever solution.

    I have a database that is protected but requires users to sort on various columns when new data is added. I have a few sites that use the database that have converted to Microsoft Teams/ Sharepoint and are now totally dependent on Excel Online. So my research tells me that Excel Online does not support Edit Ranges, or Protect/ Unprotect, and it cannot run Macros. So when I protect the database, use Edit Range to allow users to sort by using a password different from the Protected password and then use a Macro to automatically Protect again after a user event, the whole thing fails. The Online users cannot sort because they cannot enter the Edit Range password, it just says they cant sort on a protected sheet. The only way that i've found that will allow a user to sort the database is to totally remove protection, thus leaving the entire database, headers, formula etc vulnerable to accidental changes and deletion.

    Does anyone know if there is a way for online users to sort a database, but with still some kind of security/ protection to stop them making changes to formula? I want them to be able to edit the majority of the database as they need to input and change data, but i just wanted to protect headers and certain cells containing formula.
    Thanks
    Last edited by Ewen Scott; 04-20-2021 at 10:01 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Allowing sort on protected sheet without edit range or macros so can use in Excel Onli

    Can you leave the non- formula sections unprotected and just protect the columns with formulas? (I know you can by setting the protected property - I just don't know if you would want to)
    If this solves your issue, Mark thread as Solved & Add Reputation.

    Thanks!

  3. #3
    Registered User
    Join Date
    06-24-2020
    Location
    Market Harborough, England
    MS-Off Ver
    Excel 365
    Posts
    16
    Quote Originally Posted by CRIMEDOG View Post
    Can you leave the non- formula sections unprotected and just protect the columns with formulas? (I know you can by setting the protected property - I just don't know if you would want to)
    Hey Crimedog,

    I’m pretty sure that as soon as you protect you won’t be able to sort. By protecting the formula column do you just mean keeping them locked and the rest unlocked? Or is there a way to protect single columns?

  4. #4
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Allowing sort on protected sheet without edit range or macros so can use in Excel Onli

    SOrry to reply so slow - I had not turned on notifications. You are right, my idea did not work

+ 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. Allow user to edit dynamic range on protected sheet
    By PingTing in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2021, 05:21 PM
  2. [SOLVED] Need help allowing VBA to edit a protected worksheet
    By TygerG in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-06-2021, 02:17 PM
  3. Allowing Marcos To Run in a Protected Sheet
    By HankW501 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2015, 05:15 AM
  4. [SOLVED] Allowing VBA to edit protected cells
    By lucasar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2014, 08:17 AM
  5. [SOLVED] Spellcheck Macro in Protected Worksheet - allowing 'Edit Objects'
    By tigerbenn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2012, 10:48 AM
  6. Allowing functions on a protected sheet
    By prince.ali in forum Excel General
    Replies: 2
    Last Post: 11-08-2007, 09:14 AM
  7. Allowing hyperlinks an a protected sheet
    By Chris Ince in forum Excel General
    Replies: 2
    Last Post: 11-07-2005, 04:30 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