Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa

Hi
Put a button on your sheet and attach this macro:

Sub ShowAllData()

With ActiveSheet
.Unprotect Password:="Whatever"
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
.Protect Password:="Whatever"
End With
End Sub

regards
Paul
Marisa wrote:
Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa

Hi Paul,
Thanks a lot!
How to set the autofilter on again? I put the autofilter always on row 12.
Thanks again.
Marisa

" wrote:

Hi
Put a button on your sheet and attach this macro:

Sub ShowAllData()

With ActiveSheet
.Unprotect Password:="Whatever"
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
.Protect Password:="Whatever"
End With
End Sub

regards
Paul
Marisa wrote:
Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa

Hi Marisa
How did you put the autofilter on in the first place?? I thought your
users were just autofiltering as normal, and you just wanted a way to
reset everything.
I've put protection back on the sheet after removing the filters -
maybe you want to remove that line? When you protect a sheet though,
you have the option of allowing filtering.
Get back to me if you have a more subtle problem
regards
Paul

Marisa wrote:
Hi Paul,
Thanks a lot!
How to set the autofilter on again? I put the autofilter always on row 12.
Thanks again.
Marisa

" wrote:

Hi
Put a button on your sheet and attach this macro:

Sub ShowAllData()

With ActiveSheet
.Unprotect Password:="Whatever"
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
.Protect Password:="Whatever"
End With
End Sub

regards
Paul
Marisa wrote:
Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa

Hi Paul,

I want the macro would return the original autofilter setting to the user.
For example, I allow the user to use autofilter function with the worksheet
is protected. I find the additional line as below:

' showall Macro
ActiveSheet.Unprotect Password:="whatever"
ActiveSheet.ShowAllData
ActiveSheet.Protect Password:="whatever"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True
End Sub

Thanks for helping me.
Marisa

" wrote:

Hi Marisa
How did you put the autofilter on in the first place?? I thought your
users were just autofiltering as normal, and you just wanted a way to
reset everything.
I've put protection back on the sheet after removing the filters -
maybe you want to remove that line? When you protect a sheet though,
you have the option of allowing filtering.
Get back to me if you have a more subtle problem
regards
Paul

Marisa wrote:
Hi Paul,
Thanks a lot!
How to set the autofilter on again? I put the autofilter always on row 12.
Thanks again.
Marisa

" wrote:

Hi
Put a button on your sheet and attach this macro:

Sub ShowAllData()

With ActiveSheet
.Unprotect Password:="Whatever"
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
.Protect Password:="Whatever"
End With
End Sub

regards
Paul
Marisa wrote:
Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa