autofilter on open

[Guest]

I have protected worksheets with the autofilter function on one column of
each worksheet. These worksheets are generated by another "master" worksheet.
The people managing the "master" worksheet would not be able to refilter all
of the autofilters on the other worksheets.
There are many users that are not computer savvy. They forget to refilter
the information. I tried creating a button with a macro to autofilter, but
ran into a problem with the protection.
Is it possible to have all of the worksheets in the workbook to autofilter
when the various users open the workbook/worksheets?

 

[Pete_UK]

Autofilter doesn't work if the sheet is protected.

Pete

 

[Dave Peterson]

I'm not sure I understand what you want, but you could have your macro that the
button runs unprotect the worksheet, do the work and reprotect the worksheet.

If you already have the data|filter|autofilter arrows applied, you can protect
the worksheet in code so that the filtering will work.

Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
With wks
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
End With
next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

If you call the macro Auto_Open, then it will open each time excel opens that
workbook--if the user allows macros to run.

The password is embedded in the macro--I used "hi". You'd use the real
password.

If everyone is using xl2002+, you could protect the worksheet and check that
option to allow autofilter.

 

[Dave Peterson]

Read David McRitchie's notes to learn about VBA.

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Now close the workbook.
The reopen it and allow the macros to run.

I am fairly new to VB and macros. I can tell that you know your stuff.
Maybe I am putting the text in the wrong place. I went to Visual Basic and
selected the appropriate sheet. I put your text in there, replaced the
password, saved, and closed. I reopened the worksheet, but nothing happened.
Am I working in the wrong place?

I totally ditched the original macro. I had written that macro using the
record button method. The problem with that macro is that it won't run
without the password. Here's what that macro looked like:
Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 1/23/2007 by Christine Hemphill
'

'
Sheets("Master(1)").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=1, Criteria1:="<>"
Sheets("KnockDowns(5)").Select
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=10, Criteria1:="<>"
Sheets("Wire Crew(2)").Select
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=9, Criteria1:="<>"
Sheets("Color Room(1)").Select
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=7, Criteria1:="<>"
Sheets("Print Info(2)").Select
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=10, Criteria1:="<>"
End Sub

Maybe this macro can be adjusted to run without giving out the password.

 

[Dave Peterson]

Glad you got it working!

Sorry about my confusion. I just discovered what the macro you wrote did. It
allowed the autofilter without a password on the button I had created. I
didn't see that until I pushed the button. Thank you very much for your help.

 

[Dave Peterson]

That's a very nice offer.

Instead of sending it to me, maybe you could give it to a local charity (a
shelter perhaps???).

You'd even save on shipping <bg>.

You've helped me out a couple of times. My boss and I really appreciate your
help and would like to send you a case of our product. Among other things we
make Chinet plates. If you are interested, email me at
(e-mail address removed) so that I can get your address.
Thank you again.

 

[Dave Peterson]

That's very nice of you and your boss. I'm sure that they can put it to good
use.

Well, you're a good guy. I gave the info for the local homeless shelter to my
boss, and she will make sure that we donate a case of product this week.
Thanks so much. I'm sure I'll talk to you again at another time.