Protected sheet: sort macros assigned to buttons, autofilter

pmp

New Member
Joined
Sep 6, 2006
Messages
2
Using Excel 2000 (using 2k3 is not an option).
I have a sheet with a one header row and a bunch of data below it. I want to protect the sheet (actual data is not protected -- just want to prevent deletion/addition of columns).

I want the users to be able to:

1. Use autofilter
2. Sort based on certain columns by hitting a button. To do this, I added a button from the forms toolbar, stuck it in the header cell, and assigned it to a macro that unprotects the sheet, sorts, then protects the sheet.

Problem is, after protecting the sheet, I can sort it once. Then, I'm unable to click on the buttons to which I assigned the sort macros. Works fine if I remove the autofiltering, then protect it. Any ideas?

Here's the code in the sheet:

Sub Auto_Open()
With ActiveSheet
.Unprotect
.EnableAutoFilter = True
.Protect userInterfaceOnly:=True
End With
End Sub
Sub sortName()
ActiveSheet.Unprotect
Columns("A:O").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
ActiveSheet.Protect userInterfaceOnly:=True
End Sub
Sub sortCategory()
ActiveSheet.Unprotect
Columns("A:O").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
ActiveSheet.Protect userInterfaceOnly:=True
End Sub
Sub sortTYPE()
ActiveSheet.Unprotect
Columns("A:O").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
ActiveSheet.Protect userInterfaceOnly:=True
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there, Welcome to the Board

Move your buttons out of the range of the autofilter. eg put them in Q1

regards
Derek
 

pmp

New Member
Joined
Sep 6, 2006
Messages
2
Thanks for the quick reply! I will give it a go when I'm back at work tomorrow.
 

Forum statistics

Threads
1,170,930
Messages
5,872,766
Members
432,944
Latest member
mj02

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
Top