sort protected cells

[Qroozn]

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.

[KniteMare]

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

[Qroozn]

Nitremare. thanks for your help.
its taken me 6 months to get help on this problem.
I'll try it out.

[KniteMare]

np