Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Northern Territory, Australia
    Thanked 0 Times in 0 Posts

    Protection confusion (Excel 97/2000)

    With lots of help from this forum, I have managed to make a spreadsheet to maintain the annual leave records of staff in our office. It is working perfectly except for one problem I am unable to resolve.

    In the macro below, the sheets are unprotected to sort and then reprotected.

    On the Monthly Worksheets I have set up an AutoFilter on Row 4 and all Date Cells are unprotected.

    My problem is that when the macro has protected the sheets I am unable to use the Autofilter and although I can put data in the date cells, I cannot shade them. I have to unprotect each sheet individually to perform these functions, which defeats the whole purpose of protection.

    Any help is greatly appreciated, as always!

    Option Explicit
    Sub SortSheets()
    Dim sh As Worksheet
    With Worksheets("Accruals")
    .Range("A5").End(xlToRight).End(xlDown).Sort _
    Key1:=.Range("A4"), Key2:=.Range("B4"), Header:=xlNo, MatchCase:=False

    End With
    SortSheet Worksheets("Apr 03")
    SortSheet Worksheets("May 03")
    SortSheet Worksheets("Jun 03")
    SortSheet Worksheets("Jul 03")
    SortSheet Worksheets("Aug 03")
    SortSheet Worksheets("Sep 03")
    SortSheet Worksheets("Oct 03")
    SortSheet Worksheets("Nov 03")
    SortSheet Worksheets("Dec 03")
    SortSheet Worksheets("Jan 04")
    SortSheet Worksheets("Feb 04")
    SortSheet Worksheets("Mar 04")
    End Sub

    Sub SortSheet(sh As Worksheet)
    Dim rng As Range
    Set rng = sh.Range("A4").End(xlToRight).End(xlDown)
    With rng
    .Sort _
    Key1:=rng.Cells(1, rng.Columns.Count), Order1:=xlDescending, Key2:=rng.Cells(1, rng.Columns.Count - 2), _
    Key3:=rng.Cells(1, 1), Header:=xlYes, MatchCase:=False
    End With
    ColorSheet sh
    End Sub

    Sub ColorSheet(sh As Worksheet)
    Dim lngRow As Long, lngColorIndex As Long
    For lngRow = 5 To sh.Range("AK5").End(xlDown).Row
    Select Case sh.Range("AK" & lngRow).Value
    Case 1
    lngColorIndex = 40
    Case 2
    lngColorIndex = 35
    Case 3
    lngColorIndex = 37
    Case 4
    lngColorIndex = 36
    Case 5
    lngColorIndex = 15
    Case Else
    lngColorIndex = -4142
    End Select
    sh.Range("A" & lngRow & ":B" & lngRow).Interior.ColorIndex = lngColorIndex
    sh.Range("AI" & lngRow & ":AK" & lngRow).Interior.ColorIndex = lngColorIndex
    Next lngRow

    End Sub


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Protection confusion (Excel 97/2000)

    When a sheet is protected, changing formatting is disallowed, even to unprotected cells. Alas.

    But you can set the EnableAutofilter property to true after the protect method so you can use Autofilter. Note that this setting is lost when the file is closed and reopened, so you need a macro that fires on Workbook_open that sets this property to true for all sheets:

    Private Sub Workbook_Open()
    Dim oSh As Worksheet
    For Each oSh In Thisworkbook.Worksheets
    oSh.Protect contents:=True, userInterfaceOnly:=True
    oSh.EnableAutoFilter = True
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

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