Excel : Filters v Protected Sheet

[Mobbs]

Shaddup and listen.

I add an autofilter to my spreadsheet. Then I unlock all the relevant cells and protect the sheet.

Suddenly the filter doesn't work. Seems employing the filter is changing the cells.

Anyone know a way around this?

 

[sgPanther]

Will this help? (got it from the net)

Add this code to the ThisWorkbook object:

Private Sub Workbook_Open()
'Enables the AutoFilter arrows on a protected worksheet named 'mySheet'.
'Must be re-applied for each opening as property not saved

With ActiveWorkbook.Sheets("mySheet")
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
End Sub

 

[Mobbs]

Actually, much as I don't really understand that, too much, I think I might be able to work out how to apply it!

Thanks sgpanther, I think you've helped!

 

[|3ig|3alls]

Time to learn some Visual Basic

The code is quite simple, it's just a matter of getting it into the right place and excel will do the rest for you.

The steps are basically -
1. Open the spreadsheet
2. hit Alt-F11 to open VB for Applications
3. Double click on ThisWorkbook to open the code window
4. In the top left drop down window select workbook. This will create the sub for you.
5. Insert the code from above message
6. Change the MySheet to the name of your sheet
7. save and close everything.
7. Re-open and try it out.

I think that's it..

 

[|3ig|3alls]

Now it's time for you to Shaddup and listen.. how do I log out of this stupid ID and log in as my sgPanther one...

I made this name in a moment of insanity then realised how stupid it sounded and on my work PC it won't go away..

 

[Mobbs]

Go to the main forum page and there's a logout option. Choose that, then come back in via your old nickname.

Thanks for your help! I'm a QBasic whiz, but dunno much about VBasic.

Oh and ... some are held for dancing ... and some for fancy dress.

 

[Mobbs]

Legend!!!!!

It works fine!

Thanks for your help!

(!!!!!)

 

[sgPanther]

glad to be of service.. your bills in the mail..