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?

Autofilter doesn't work if the sheet is protected.

Pete

Christine Hemphill wrote:

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?

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

Christine Hemphill wrote:

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?

--

Dave Peterson

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Glad you got it working!

Christine Hemphill wrote:

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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
so that I can get your address.
Thank you again.

"Dave Peterson" wrote:

Glad you got it working!

Christine Hemphill wrote:

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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.

Christine Hemphill wrote:

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
so that I can get your address.
Thank you again.

"Dave Peterson" wrote:

Glad you got it working!

Christine Hemphill wrote:

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

Christine Hemphill wrote:

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
so that I can get your address.
Thank you again.

"Dave Peterson" wrote:

Glad you got it working!

Christine Hemphill wrote:

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

Christine Hemphill wrote:

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.

"Dave Peterson" wrote:

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.

Christine Hemphill wrote:

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
so that I can get your address.
Thank you again.

"Dave Peterson" wrote:

Glad you got it working!

Christine Hemphill wrote:

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" wrote:

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.


Christine Hemphill wrote:

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" wrote:

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.

Christine Hemphill wrote:

I can't use a macro because I need to set it up so that anyone can open the
file and either run the macro or have the autofilter run for them without
them having to remember to use the autofilter.
In order for them to run the macro, they would need the password, as far as
I know. I've tried to create the macro to unprotect the sheet, do the
autofilter, then reprotect the sheet. When I try to run that macro, it still
asks for the password. I cannot give out the password to everyone.
I've been told that you can get the sheet to do the autofilter automatically
when you open the worksheet, but I can't seem to find anyone that knows how
to do it.

By the way - I did see that link on another one of your postings I have
added it to my favorites. In that website I discovered that you cannot run a
macro without unprotecting the worksheet.

"Dave Peterson" wrote:

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

Christine Hemphill wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson