Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Password Protection

  1. #1
    Guest

    Default

    I have a spread sheet that I have password protected from changes in the format but it will not allow users to sort. Is there a way to protect the format and still allow sorting?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Two options.

    1. Get Excel XP.
    2. Make a macro to unprotect the sheet, sort, reprotect the sheet.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Guest

    Default

    Thanks for the info, I tried the macro and it still asks for the password. and unfortunately the office is not ready to go XP for all of us yet

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Ermm i have tried this one and yep E97 dont like it XP will allow >> Update and improvements but that no help to you, a fix you need.

    All i can think of is a macro to switch OFF all protection to allow sort and then bang protection on at end of script.. thus no other amendments can be made, ie you trigget the sort and close protected on command button

    Any good?


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #5
    Guest

    Default

    I don't think I understand, this is the macro I wrote, but it still asks for the password when you run it in Excel 2000

    ,Sub DateSort()
    '
    ' DateSort Macro
    ' Macro recorded 2/26/2002 by PEileen
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    ActiveSheet.Unprotect
    Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    I really do appreciate any help you can give, these users are driving me nuts on this!

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the sheet module put:

    Private Sub Worksheet_Change(ByVal Target As Range)
    '
    ' Macro by Joseph S. Was
    '
    Application.EnableEvents = False

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
    Application.EnableEvents = True
    End Sub

    Then in a module add the unlock code and attach your sort code, befor the End Sub.

    Sub myUnLock()
    'This code will unprotect the sheet.
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    ActiveSheet.Unprotect ("admin")

    'Add optional code here!

    Application.EnableEvents = True
    Application.DisplayAlerts = True

    End Sub

    Then run your code out of the unlock sub, the first macro will password lock the code behind you. Note: this uses hard coded password protection which will decrease the security of the protection. But then again a password does not really deter someone from getting in if they want to, like a door lock does not stop some one from getting in a window, but does stop the causal passer-by from coming in. JSW

  7. #7
    Guest

    Default

    Thank you! Thank you! Thank you! I really appreciate your help

Some videos you may like

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
  •