Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Protection (2000 SR1)

    I am needing to protect a portion of a sheet. I have used Format Cells and unselected the Lock option for the cells that I don't want protected. After protecting the sheet I can alter the unlocked cells but I can't sort them. Is there a way to sort after protecting a sheet?

    Also, is there a way to allows users to make changes to cells but not delete rows or columns?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    Once a sheet is protected, then sort is disabled. The only way to sort would be to unprotect, sort, and then protect it again. You could do all of that in a macro.

    If you protect anything on a sheet, then deleting rows and columns should be disabled.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    Thank you. I found a trigger on the worksheet that will fire is someone should change the sheet. Is there a way to limit it to just rows or columns being deleted.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    What event are you looking at? If it is the worksheet change event, I do not think it fires on a delete.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    I was looking at the change event and was hoping that I could get it to recognize 'delete'.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    Is the functionality of Data|Filter|Autofilter good enough for your purposes ? If yes, I can give you macro which will allow to use this feature on a protected worksheet.

  7. #7
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    Could you please post your solution for others. I am looking for a method to allow one of my users to use auto filtering on a protected sheet. Your solution may fit my needs as well.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection (2000 SR1)

    You can enable Autolist and Grouping by VBA. However this is not saved with your workbook. Therefore the simplest way is to put this code on ThisWorkbook level of your workbook and adapt MySheetName to your database worksheet. If you want all sheets to be processed make a Loop.

    Private Sub Workbook_Open()
    'Make sure that Grouping and Autofilter in MySheetName are operational/reset
    With Worksheets("MySheetName")
    .EnableOutlining = True
    .Protect contents:=True, DrawingObjects:=True, userInterfaceOnly:=True
    .EnableAutoFilter = True
    'If you want to expand the autofilter when starting, adapt and activate next line
    '.Range("PutName_OR_YourRange").AutoFilter Field:=1
    MsgBox "Autofilter and Grouping in " & .Name & " now operational" 'inactivate if too bothersome
    End With
    End Sub

Posting Permissions

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