Forum Discussion

Bkkendig's avatar
Bkkendig
Copper Contributor
Jun 17, 2023

Excel script to pause worksheet protection and allow data sort based on a specific column

I have a spreadsheet that is password protected.  It contains a matrix of data (not formatted as a table), and the first row of the matrix contains data heading labels.  The filter is turned on and any column can be filtered, even with protection turned on.  My dilemma is I cannot sort the matrix while protection is turned on.  Does anyone know a script that would pause protection, sort the matrix (alpha, ascending) based on a specific column, then resume protection?  

Excel for web
Office Scripts

3 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 17, 2023

    Bkkendig 

    You can use the Office Scripts feature in Excel for the web to create a script that pauses worksheet protection, sorts the data based on a specific column, and then resumes the protection.

    Here is an example script that you can use:

    function main(workbook: ExcelScript.Workbook) {
  // Get the active sheet
  let sheet = workbook.getActiveWorksheet();

  // Pause worksheet protection
  sheet.protect();

  // Sort the data
  let dataRange = sheet.getUsedRange();
  let sortColumn = "A"; // Replace with the column letter you want to sort on
  let sortRange = dataRange.getRangeByIndexes(1, 1, dataRange.getRowCount(), dataRange.getColumnCount());
  sortRange.sort({ key: sortColumn, ascending: true });

  // Resume worksheet protection
  sheet.unprotect();
}

     

    To use this script:

    1. Open your Excel spreadsheet in Excel for the web.
    2. Click on the "Automate" tab in the ribbon.
    3. Click on the "Script Lab" button. If you do not have the Script Lab add-in installed, you can find it in the Office Add-ins store and add it to your Excel for the web.
    4. In the Script Lab pane, click on the "Create a new script" button.
    5. Replace the existing code with the code provided above.
    6. Modify the sortColumn variable to specify the column letter you want to sort on. For example, if you want to sort on column B, change let sortColumn = "A"; to let sortColumn = "B";.
    7. Click on the "Run" button to execute the script.

    The script will pause the protection, sort the data based on the specified column in ascending order, and then resume the protection. Make sure to save your changes after running the script.

    Note: Office Scripts is currently available only for Excel for the web and requires a Microsoft 365 subscription.

    • Bkkendig's avatar
      Bkkendig
      Copper Contributor
      Jun 17, 2023
      Thank you. I shall give this a try. Is it possible that the sequence of "protect" and "unprotect" in your script should be reversed?
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Jun 17, 2023

        Bkkendig 

        function main(workbook: ExcelScript.Workbook) {
  // Get the active sheet
  let sheet = workbook.getActiveWorksheet();

  // Unprotect worksheet
  sheet.unprotect();

  // Sort the data
  let dataRange = sheet.getUsedRange();
  let sortColumn = "A"; // Replace with the column letter you want to sort on
  let sortRange = dataRange.getRangeByIndexes(1, 1, dataRange.getRowCount(), dataRange.getColumnCount());
  sortRange.sort({ key: sortColumn, ascending: true });

  // Protect worksheet
  sheet.protect();
}

        The worksheet is first unprotected using sheet.unprotect(), then the data is sorted, and finally, the worksheet is protected again using sheet.protect().

        Please use this updated script to pause worksheet protection, sort the matrix based on a specific column, and then resume protection.

Resources