none
Enable the Sorting, Filtering, Hide and Unhide in Protected Worksheet RRS feed

  • Question

  • Hi

    i am using the .Net Excel AddIn, how can i enable the Sorting, Filtering, Hide and Unhide in Protected Worksheet from code.

    Thanks

    Ankur R.

    Wednesday, March 15, 2017 6:55 AM

All replies

  • Hi ankurastogi,

    First you need to unprotect the worksheet, you cannot perform anything without unprotecting it.

    you can use code below to unprotect the worksheet.

      ((Excel.Worksheet)Application.ActiveSheet).Unprotect(getPasswordFromUser);
    

    How to: Programmatically Remove Protection from Worksheets

    Refer examples below for sorting, filtering and hide/ unhide.

    Sorting example:

    Excel.Range Fruits = Application.get_Range("A1", "B3");
                    Fruits.Sort(
                    Fruits.Columns[1], Excel.XlSortOrder.xlAscending,
                    Fruits.Columns[2], missing, Excel.XlSortOrder.xlAscending,
                    missing, Excel.XlSortOrder.xlAscending,
                    Excel.XlYesNoGuess.xlNo, missing, missing,
                    Excel.XlSortOrientation.xlSortColumns,
                    Excel.XlSortMethod.xlPinYin,
                    Excel.XlSortDataOption.xlSortNormal,
                    Excel.XlSortDataOption.xlSortNormal,
                    Excel.XlSortDataOption.xlSortNormal); 
    


    Sorting Data in a VSTO Add-in

    example of filtering:

    private void ActivateAutoFilter()
    {
        this.Range["A1"].Value2 = "Fruits";
        this.Range["A2"].Value2 = "Banana";
        this.Range["A3"].Value2 = "Apple";
        this.Range["A4"].Value2 = "Banana";
        this.Range["A5"].Value2 = "Orange";
        this.Range["A6"].Value2 = "Apple";
        this.Range["A7"].Value2 = "Orange";
    
        Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
            this.Controls.AddNamedRange(this.Range["A1", "A7"],
            "NamedRange1");
    
        NamedRange1.AutoFilter(1, "Apple",
           Excel.XlAutoFilterOperator.xlAnd, true);
    
        MessageBox.Show("The current criteria for the AutoFilter is: " +
            this.AutoFilter.Filters[1].Criteria1.ToString());
    
        if (this.AutoFilterMode)
        {
            if (DialogResult.Yes == MessageBox.Show("Would you like to " +
                "turn off the filter?", "Example", MessageBoxButtons.YesNo))
            {
                this.AutoFilterMode = false;
            }
        }
    
        if (this.FilterMode)
        {
            MessageBox.Show("The worksheet has a filtered list.");
        }
        else
        {
            MessageBox.Show("The worksheet does not have a filtered list");
        }
    }
    

    WorksheetBase.AutoFilter Property

    example for hide / unhide worksheet:

       Globals.Sheet1.Visible = Excel.XlSheetVisibility.xlSheetHidden;
    
    or
    
      ((Excel.Worksheet) Globals.ThisWorkbook.Sheets[1])
                    .Visible = Excel.XlSheetVisibility.xlSheetHidden;
    

    How to: Programmatically Hide Worksheets

    Regards

    Deepak

    Thursday, March 16, 2017 1:09 AM
    Moderator