Macro on close

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Skin Paul - 12 Jul 2007 08:49 GMT

Hi all
Excel 2004 11.3.5 On intel imac 4.10.10
Is it possible when I close a sheet or save the workbook it will Auto run a
macro to turn off AutoFilter. I sometimes forget to do it and have to reopen
sheet to turn off. I do not want other users to see filtered lists if they
open this workbook.
Paul

Reply to this Message

Bob Greenblatt - 12 Jul 2007 12:38 GMT

On 7/12/07 3:49 AM, in article C2BC1A38.DF8%asking@optusnet.com.au, "Skin
Paul" <asking@optusnet.com.au> wrote:

> Hi all
> Excel 2004 11.3.5 On intel imac 4.10.10
[quoted text clipped - 3 lines]
> open this workbook.
> Paul

Sure,

Go to the code page of the thisworkbook in question, and use the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
worksheets("whatevernameyouhave").showalldata
End Sub

If more than 1 sheet is filtered, add a line for each sheet or a loop for
all sheets:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
For each xx in thisworkbook.worksheets
xx.showalldata
next
End Sub

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Skin Paul - 12 Jul 2007 15:46 GMT

Thanks Bob. Much appreciated
Paul

On 12/7/07 9:38 PM, in article C2BB8AF2.98331%bob@nospam.com, "Bob
Greenblatt" <bob@nospam.com> wrote:

> On 7/12/07 3:49 AM, in article C2BC1A38.DF8%asking@optusnet.com.au, "Skin
> Paul" <asking@optusnet.com.au> wrote:
[quoted text clipped - 22 lines]
> next
> End Sub

Reply to this Message

Mac Forum / Applications / Excel / July 2007

Macro on close