i have many columns of data using the autofilter. is there a shortcut
that either returns all the fields to the all function or a shortcut
that returns all the data to the page

thanks

One line of code will do this (maybe put in your Personal.xls):

Sub Foo()
Activesheet.ShowAllData
End Sub

Then assign it to a New (Macro) Icon in your toolbar.
It will always be available on any sheet that has an autofilter
range assigned.

HTH

"derwood" wrote in message
oups.com...
i have many columns of data using the autofilter. is there a shortcut
that either returns all the fields to the all function or a shortcut
that returns all the data to the page

thanks

Hi

ToolsCustomiseCommandsDatadrag Show All to your Toolbar.
If you want to give it an icon, whilst in this mode, right clickChange
Button image.

Regards

Roger Govier


derwood wrote:
i have many columns of data using the autofilter. is there a shortcut
that either returns all the fields to the all function or a shortcut
that returns all the data to the page

thanks

thanks but is there a way to do this when the sheet is protected as well

Hi

I just tried it on a Protected sheet and it allowed me to do so.
Excel 2003

Regards

Roger Govier


derwood wrote:
thanks but is there a way to do this when the sheet is protected as well

xl2002+ has an option to allow you do use autofilter on a protected worksheet.

In earlier versions...

If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

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

derwood wrote:

thanks but is there a way to do this when the sheet is protected as well

--

Dave Peterson

hi roger
maybe im doing something wrong here
loading show all in the task bar
selecting protect sheet
function turns off
then unprotecting sheet
function turns on

do you know of any other ways to do this
i also use excel 2003

Hi Darren

I apologise. Yes, I was able to drag the ShowAll icon to he task bar whilst
in protect mode, but of course it doesn't work whilst protection is invoked.
Dave Peterson posted saying that you can enable Autofilter when setting
Protection on in XL2002 upward, and showed some code for what to do with
earlier versions.

Following Dave's lead, you will need to add the following code to the
workbook to be triggered on opening,

Sub auto_open()
With Worksheets("sheet1")
ActiveSheet.Protect Password:="roger", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowFiltering:=True
End With
End Sub

Then also add this code and attach it to a button which you can add to your
toolbar
Sub ShowAll()
ActiveSheet.Unprotect Password:="roger"
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
ActiveSheet.Protect Password:="roger", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowFiltering:=True
End Sub

Change the password to match that you currently use for the Sheet.
The On Error lines are to trap against a failure if you press the button
when a filter is not in place.

Sorry for misleading you with earlier posting.

Regards

Roger Govier


derwood wrote:
hi roger
maybe im doing something wrong here
loading show all in the task bar
selecting protect sheet
function turns off
then unprotecting sheet
function turns on

do you know of any other ways to do this
i also use excel 2003