Prohibit sorting in a Worksheet

**Carlsbergen** · 10-28-2009, 03:55 AM

Hi.

This might sound like a weird question, but is there any way to prohibit a user to using the sort function in a particular sheet?
Filtering for values is okay but sorting must be prohibited so that no rows "change place" in a sense.

Any ideas?

**Richard Buttrey** · 10-28-2009, 05:33 AM

Hi,

The following line of code should disable the Data Sort menu item for the whole workbook, but not just a sheet. You could password protect the sheet from changes but of course this will also prevent filtering.

Please Login or Register  to view this content.

You will need to re-instate it before quitting the s/s with .Enabled = True

(acknowledgement to Ron de Bruin)

Rgds

**Carlsbergen** · 10-28-2009, 07:49 AM

Originally Posted by Richard Buttrey

Hi,

The following line of code should disable the Data Sort menu item for the whole workbook, but not just a sheet. You could password protect the sheet from changes but of course this will also prevent filtering.

Please Login or Register  to view this content.

You will need to re-instate it before quitting the s/s with .Enabled = True

(acknowledgement to Ron de Bruin)

Rgds

Okay.

Hmm..

Is it possible to run a macro everytime you select a sheet that turns this thing on and off? That is the only way I can think of that would make this work the way I want.

**Richard Buttrey** · 10-28-2009, 09:01 AM

Yes of course.

There is a Worksheet_Activate, and a Worksheet_DeActivate event in the VBE that you could use. These would automatically grey out the Data Sort option every time the user moved to the sheet and make it available when the sheet is not the active sheet.

HTH

**Carlsbergen** · 10-29-2009, 04:00 AM

Originally Posted by Richard Buttrey

Yes of course.

There is a Worksheet_Activate, and a Worksheet_DeActivate event in the VBE that you could use. These would automatically grey out the Data Sort option every time the user moved to the sheet and make it available when the sheet is not the active sheet.

HTH

Works great!

Thanks alot for your help