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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,137,292
Messages
5,680,645
Members
419,923
Latest member
Kalthus

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