Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One query,
when you write that the macro has to be in the Workbook.Open Event, what is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey


"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.

Apologies to all, I have just noticed that my newsreader has put my name as
'Microsoft' That was an unintentional error,

Apologies,
Mickey

Hi Mickey,
Open the VBA editor. In the top left window you will see a list of objects
(add-ins, workbooks, sheets).
If its not already expanded, expand the list under VBA Project
(yourworkbookname.xls) and you will see a listing for "thisworkbook".
Double click it and a module will open. In the left dropdown of the code
window, select "workbook".
Excel should place this in the code window.

Private Sub Workbook_Open()

End Sub

Add your event code inside the sub and it will execute every time the
workbook is opened.
You can disable the open event by holding down the shift key while opening
the workbook.


--

John

johnf202 at hotmail dot com


"Microsoft" wrote in message
...
Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One
query,
when you write that the macro has to be in the Workbook.Open Event, what
is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey


"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.

Mickey,

It can be in the Workbook.Open Event or a regular sub named
Auto_Open

Easiest way would be to use the Auto_Open
In place of
Sub ProtectFromStickyFingers()
use
Sub Auto_Open()

Place this in a regular module.
e.g.
Alt + F11
Right click on "ThisWorkbook" in the "Projects" window
Select "Insert/Module" (the module panel will appear to the right)

Now copy and paste the folowing into that module:

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End With
End Sub

The above will run each time the workbook is opened and
protect "Sheet1" with UserInterFaceOnly = True (meaning
that your code can modify cells on the sheet but the user can't)
and EnableAutoFilter = True (meaning that the AutoFilter should
work).

John

Microsoft wrote:

Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One query,
when you write that the macro has to be in the Workbook.Open Event, what is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey

"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.