Protected sheets, groups and filtering - driving me nuts.

[muzzanoosa]

I have a couple of documents with similar requirements.

Locked (so nobody can screw up my functions/formulas)
The document will need to be accessed by all the teachers in the department to enter raw data.
Expand and collapse groups of columns (there are a LOT of columns)
Use Autofilter (There will eventually be a lot of rows)
Need the above to work on more than one sheet.

I've been using VBA, but I am by no means knowledgeable. I'm just scouring the internet and copying other peoples codes.

BUT, I can't get a 100% solution. When I turn protection on, I either get filtering with no active grouping/ungrouping, or vice versa, or one tab works perfectly and the other one has a glitch. I go in, fix the glitch, save, close reopen and find that that tab now works, but the other one now has a glitch.

I finally found a code that enables grouping on more than one sheet with protection enabled;

Private Sub Workbook_Open()
For Each ws In Sheets
With ws
.Unprotect Password:="password"
.Protect Password:="password", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next ws
End Sub

Problem is, now filtering doesn't work.

I ensure Autofilter and Sort are ticked when I protect the sheet, and it works while the file is open.

But, if I save, close and reopen the file, filtering is turned off. The arrows are still there, but they don't do anything. Groups can still be expanded and collapsed.

When I go into the protect sheet dialog, Autofilter and Sort are now unticked. I think the VBA is turning it off?

I've been trying to sort this out for 2 days now. I'm at my wits end.

Any ideas?

Thanks in advance.

 

[alvinwlh]

Hi,

Just add in

[FONT=Courier New]Private Sub Workbook_Open()
For Each ws In Sheets
With ws
.Unprotect Password:="password"
.Protect Password:="password", UserInterfaceOnly:=True[COLOR=red], AllowFiltering:=True
[/COLOR].EnableOutlining = True
End With
Next ws
End Sub[/FONT]

 

[muzzanoosa]

OK. I'm back. This project went onto the back burner for a while and now I'm back on it.

Thanks for your reply Alvin, but it still doesn't work. I'm assuming that "password" should be the password that I've set for the document.

The collapsing/expanding columns works after protecting, but filtering is still a greyed out non-starter.

I've uploaded the file for you Excel wizards to have a look at and tell me what I'm doing wrong. The password is "mgs"

Here is the link for the file; http://www.4shared.com/file/FsM7IORZ/results_sheet.html

(make sure you download the file, not the program!)

thanks in advance.

Muz

 

[muzzanoosa]

Something I forgot to mention...
The file seems to work as intended immediately after applying protection, but when I close the excel and re-open the file, the filtering stops working.

 

[muzzanoosa]

Also, the expanding/collapsing and filtering are only required on the first two tabs.

 

[muzzanoosa]

Bump

 

[muzzanoosa]

Ok, I've sorted it out! (Only took me 8 months)

The code was correct all along, but I only added 'AllowFiltering:=True' to the Sheet 1 code. Once I added it to 'ThisWorkbook' it started working.

Yoohoo!!!

Newbie mistake?