Re-protect an Excel sheet, enabling Auto-Filters

Question

I'm using VB.net 2005 to input data from a database application into Excel files.

I un-protect the sheets, input data into specific cells using named ranges, then re-protect the sheets.

This has worked fine so far, up until I ran into a file that is protected with auto-filtering enabled. When I re-protect the sheets, I can't seem to protect it while allowing the use of autofilter dropdowns. The dropdown arrows for the autofilter are greyed out and disabled.

Using the following code to re-protect the sheets after inputting the data.

If bolProtected = True Then
   For i = 0 To intProtectIndexes.Length - 1
      objExcel.Sheets(intProtectIndexes(i)).Select()
      objExcel.ActiveSheet.Protect(strPassword)
   Next
End If

Also tried using the code from Tim's link.

objExcel.Sheets(intProtectIndexes(i)).Select()
objExcel.ActiveSheet.Protect(Password:=strPassword, Contents:=True)
objExcel.activesheet.enableautofilter = True

This still leaves the autofilter drop downs disabled on the populated worksheet.

Creating a macro in VBA and using:

ActiveSheet.Protect(Contents:=True, AllowFiltering:=True)

works fine. The sheet is protected and I can use the autofilter drop downs. But when I use that same code in my VB.net project, the drop downs are not enabled.

What do you mean with "can't seem to protect it while allowing the auto filtering option"? Maybe this helps you: office.microsoft.com/en-us/excel-help/… — Tim Schmelter
When I programmatically re-protect the sheets, any auto-filters that were in place before inserting the data are no longer usable. I think the link you gave has the answer. Make an answer, I'll test it and see if it works and give you credit. — KevenDenen

Reafidy Reafidy · Accepted Answer · 2011-09-13T01:57:20

Try

With objExcel.Sheets(intProtectIndexes(i))
    .Protect(Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True)
    .EnableAutoFilter = True
End With

Or

With objExcel.Sheets(intProtectIndexes(i))
    .Protect (Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True)
End With

Re-protect an Excel sheet, enabling Auto-Filters

1 Answers