sort protected cells

Qroozn

Well-known Member
Joined
Mar 12, 2002
Messages
543
HI all,
I have a database in excel. i want to sort this database, but at the same time make sure noone can edit any records. by protecting the cells it stops them being sorted, any help is greatly appreciated.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
This goes in the code window for the sheet.


Public Sub SortMyList()
ActiveSheet.Unprotect password:=("MyPassWord") 'runs the unprotect routine Substitute your password for MyPassWord
'Now we put all the names in alphabetical order
Range("A1:A17").Sort key1:=Range("A1:A17"), order1:=xlAscending
'This sorts the Range and puts in in order a to z
ActiveSheet.Protect password:=("MyPassWord"), DrawingObjects:=True, Contents:=True, Scenarios:=True
'password protects just like unprotect the passwords MUST be identical
End Sub

You could create a button to run it or even set the routine to require a password to run like this:

Public Sub MyButton_Click()
Dim MyStr1 As String, MyStr2 As String
With ActiveSheet 'This is what ever sheet you are working with
MyStr2 = ("SomePassWord") 'The password and it is CASE sensitive
MyStr1 = InputBox("Enter Password") 'this lets the user type in a password
'While this inputbox echos what you type there are ways to make this return ** _
or any char. you choose but it is more complex.
If MyStr1 = MyStr2 Then 'this compares the fixed password to whatever the user typed
SortMyList 'if they got it right the SortMyList routine will run
Else ' if they did not get the password right...
MsgBox ("Access Denied") 'they are denied access to the function
End If
End With
End Sub
 
Upvote 0
Nitremare. thanks for your help.
its taken me 6 months to get help on this problem.
I'll try it out.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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