Force a Sort when closing a worksheet?

[Jean]

I've created a macro to do the sort on a worksheet, now I
want to force this sort to happen each time the worksheet
is opened or closed. Also need to disable the user from
being able to do any kind of manual sort. Is this possible?

 

[Don Guillett]

Have a look at the Before Close event in ThisWorkbook module

 

[kkknie]

You can put a call to the code that does the sorting in th
Workbook_Open event. This code needs to go in the worksheet section o
the VB editor (i.e. not a module and not a sheet). It would loo
something like this:

Private Sub Workbook_Open()

Call MySortingSubroutine

End Sub

If it is in the open, you probably don't need it in the close, since i
will happen every time.

As for protecting against sorts, I am running Excel 2002 and if
choose to protect a sheet, one of the options is Sort. I've never use
it, but I would guess that's what its for.

You would need to unprotect it in code when you do your automatic sort
with:

Sheets("YourSheetName").Unprotect Password:="yourpassword"

 

[Frank Kabel]

Hi Jean
as a general idea:
- protect your worksheet to prevent sorting by your users
- put some code in the workbook_open and workbook_Beforeclose event:
-> unprotect the sheet
-> do your sorting
-> protect the sheet again
-> within the workbook_beforeclose event also save the workbook