Sort ascending or descending in protected worksheet

[Roady]

Hi:
I have an Excel document that is protected. There are macros buttons on the
page which contain un-password protect and then re-protecting at the end to
allow it to perform certain functions for the end-user. However, when I try
to sort ascending or descending, it does not allow it. What is the code for
this?

thank you,
Jen

 

[Dave Peterson]

You should be able to record a macro (after you've unprotected the sheet) to
sort either ascending or descending.

Then you'll have the code for that portion.

 

[John C]

When protecting a sheet, there is actually a checkbox for SORT. I recorded a
very quick macro, after the statement
ActiveSheet.Protect
you probably have several items here but you would want to include
AllowSorting:=True

 

[Roady]

Hi John and Dave:

I have done both of what you have said, however it gives me the following
error message after being re-protected, "The cell or chart you are trying to
change is protected and therefore read-only. to modify a protected cell or
chart, first remove protection using the Unprotect Sheet command...etc."
Just so I am clear, I want the user to be able to sort and/or filter AFTER
the macro has been run and the sheet is re-protected. In the protect sheet
check box selections, I do select 'allow sort' but somehow it never allows
it. I have the following coding in my macro that should allow that but
somehow does not work:
ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True

Another weird thing is that even though in the check boxes I select 'Allow
Column Formatting' and 'Allow Row Formatting', it unchecks it after
re-protecting again. HELP! Thanks.

 

[Roady]

Hi John and Dave:

I have done both of what you have said, however it gives me the following
error message after being re-protected, "The cell or chart you are trying to
change is protected and therefore read-only. to modify a protected cell or
chart, first remove protection using the Unprotect Sheet command...etc."
Just so I am clear, I want the user to be able to sort and/or filter AFTER
the macro has been run and the sheet is re-protected. In the protect sheet
check box selections, I do select 'allow sort' but somehow it never allows
it. I have the following coding in my macro that should allow that but
somehow does not work:
ActiveSheet.Protect Password:="ABCD", AllowFiltering:=True, AllowSorting:=True

Another weird thing is that even though in the check boxes I select 'Allow
Column Formatting' and 'Allow Row Formatting', it unchecks it after
re-protecting again. HELP! Thanks.

 

[Dave Peterson]

Make sure that all the cells in the range to be sorted are unlocked.

Or just provide a macro that would unprotect the sheet, sort the data, reprotect
the sheet.

 

[Roady]

Hi Dave: unfortunately, I can't make sure all the cells to be sorted are
unlocked because then there is no point in protecting the document. The user
would be able to delete out the contents of the cells which is what I need to
prevent. Does that make sense?

I am not sure what macro I could create. I have about 32 columns with
auto-filters across so would I then need to create a macro for each column? I
don't want to create a macro that will simply unprotect, because that leaves
the data too vulnerable. Ideas?

 

[Dave Peterson]

How about an alternative?

Use invisible rectangles over the headers that allows you to sort your data
based on the column of the rectangle that you clicked.

If you want to try that, check out Debra Dalgleish's site:
http://contextures.com/xlSort02.html

You can add a couple of lines to the sort routine--one to unprotect the sheet
and one to reprotect the sheet.