Protect Sheet & still use the sort with Autofilter

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
I have a sheet where columns B3:E65536 are unlocked.
I want to be able to password protect the sheet & still be able to use the sort feature with Autofilter.
Can this be acheived with a Macro & if so any ideas on how I could do this?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Phixtit:

I had a similar problem before. When you protect the sheet you need to ADD a couple of check marks under the section

"Allow all users of this worksheet to:"

Check off "Sort" and "Use Auto Filter" and this may resolve your problem.

As for the VBA Code here is what I get when I record the macro as described above. Hopefully you can implement it:

Code:
Sub ProtectWithAutoFilterAndSortCapabilities()
'
' ProtectWithAutoFilterAndSortCapabilities Macro
' Macro recorded 27/05/2009
'
'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
End Sub

Good Luck. Hope this helps. :biggrin:
Mark
 
Upvote 0
Hi Phixtit:

I had a similar problem before. When you protect the sheet you need to ADD a couple of check marks under the section

"Allow all users of this worksheet to:"

Check off "Sort" and "Use Auto Filter" and this may resolve your problem.

As for the VBA Code here is what I get when I record the macro as described above. Hopefully you can implement it:

Code:
Sub ProtectWithAutoFilterAndSortCapabilities()
'
' ProtectWithAutoFilterAndSortCapabilities Macro
' Macro recorded 27/05/2009
'
'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
End Sub

Good Luck. Hope this helps. :biggrin:
Mark

I have tried as you suggested & when I protect the sheet and try to sort with autofilter it says:
The cell or chart you are trying to change is protected and therefore read only. etc.

As for the Macro... I pasted it in the sheet code via alt F11.

Am I missing something?
 
Upvote 0
Hi:

Is your AutoFilter already on BEFORE you protect your sheet or are you trying to set it up after the sheet is protected? Do you have other VBA Code in your sheet? Is one of the VBA Codes suppose to set up and activate your AutoFilter?

On my spreadsheet I have the AutoFilter set up and on and then my sheet is protected and my AutoFilter still works the way it was set up. Maybe if you can provide a little more detail then I may be able to assist further. Sorry for the confusion... :)

If worse comes to worse maybe you could email (if it is not confidential) the spreadsheet and I could have a look. Hopefully you can get this working for you.

Bye 4 Now,
Mark :)
 
Upvote 0
Hi - I have exactly the same problem (using 2003). Autofilter is set before protection, and when protecting, I tick to allow users to Sort and to Use Autofilter. After protection, the filter options work in all ways except one - it won't sort by Ascending or Descending.

Any ideas any-one?
 
Upvote 0
Hi Sue:

My understanding is that the Sort will not work unless you Unprotect the sheet (even when AutoFilter has been set up). From what I understand you would have to Sort using a macro that would Unprotect, sort, and the re-protect your sheet. Maybe I am wrong and someone else can jump in and correct me... :)

Are you only looking to sort on a specific column or are there multiple sorts that are required?

If the Board knows all your needs you may get some suggestions.

Have a GREAT day,
Mark :biggrin:
 
Upvote 0
Thanks for that Mark. The specific purpose I am using this for does not warrant going to the extent of setting up a macro, and the protection is only to prevent accidental overwriting of formulas, so I will just show this user how to unprotect when needed. However, I will probably want to find a way around this in the future, so will post again at that stage.

Thanks for your reply. Sue
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top