sort protected cells
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: sort protected cells

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    np

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com