USD $50.00 Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

GoCavs · Oct 27th 2010

I am looking to achieve the following 3 actions on a workbook (see attached) I am designing:

1) Sort on the data table on the sheets (Range A2:CM87). The range may change if new rows are added or deleted so if can be done dynamically, that would be ideal. I would love to accomplish the sorting by the following method I found from another site
[INDENT]Method that allows to sort easily by clicking on the header.

You could assign a macro to hidden rectangles in the header rows. The macro
would unprotect the sheet, sort by that column and reprotect the sheet.

Sample code and file found at Debra Dalgleish's site:
http://www.contextures.com/xlSort02.html
[/INDENT]I attempted to use this in my file and received errors. It seems pretty straightforward so I'm not sure why I am getting errors but I am a novice at VB. I would like to have all 91 columns to have the sorting capability.

2) Allow Filtering on the data table

3) Allow users to Expand/Collapse Groupings on the Sheets

I have some Workbook_Open code in the file which allows Outlining but doesn't allow Filtering, Sorting, etc. after the workbook is opened up.

Code

Private Sub Workbook_Open()
    With Sheet1
       .Protect Password:="", UserInterfaceOnly:=True
       .EnableOutlining = True
 
    End With
    With Sheet2
       .Protect Password:="", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
End Sub

Display More

I attached the sample workbook that I am working with. It's password protected but I didn't use a specific password = "". Any help will be greatly appreciated. I looked for hours trying to find answers for all of these and I can't seem to get it all working.

Rob Xaos · Oct 27th 2010

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

I will look at this

GoCavs · Oct 27th 2010

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

For reference, the sample code for Excel: Sort Data With Invisible Rectangles

"Thanks to Dave Peterson, who wrote this technique.
To make it easier for users to sort Excel data, and to help prevent scrambled data, you can add invisible rectangles at the top of your columns. When a rectangle is clicked, a macro runs.
In the macro, the whole data range is selected, and the data is sorted by the column in which the rectangle was clicked. The zipped sample workbook can be downloaded here: SortClick.zip
Create the Invisible Rectangles

Run the following code once, to create the rectangles at the top of the table. You can change the iCol variable, to match the number of columns in your table.
To run the code, copy it to a regular code module, then in Excel, choose Tools | Macro | Macros. Select the SetupOneTime macro, and click the Run button..

Code

Sub SetupOneTime()
 
'adds rectangle at top of each column
'code written by Dave Peterson 2005-10-22
  Dim myRng As Range
  Dim myCell As Range
  Dim curWks As Worksheet
  Dim myRect As Shape
  Dim iCol As Integer
  iCol = 10  '10 columns -- change this to the number of 
                   'columns in your table
 
  Set curWks = ActiveSheet
 
  With curWks
 
    Set myRng = .Range("a1").Resize(1, iCol)
    For Each myCell In myRng.Cells
        With myCell
          Set myRect = .Parent.Shapes.AddShape _
              (Type:=msoShapeRectangle, _
              Top:=.Top, Height:=.Height, _
              Width:=.Width, Left:=.Left)
        End With
        With myRect
          .OnAction = ThisWorkbook.Name & "!SortTable"
          .Fill.Visible = False
          .Line.Visible = False
        End With
    Next myCell
  End With
End Sub

Display More

Rob Xaos · Oct 27th 2010

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

GoCavs,

Your last code post has got badly mangled and as such is unreadable. Can you please use Edit Post and re insert it.

Are you asking that this code is used or is this now a solution so you don't need this done?

GoCavs · Oct 27th 2010

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

Sorry about that. Below is the corrected version. This can be found at http://www.contextures.com/xlSort02.html as well. I found an sample file with this method as well for reference (attached). No, I do not have a solution but thought this example would help to make it easier to solve with it. I tried using the macros in the sample in my file but kept getting errors. Thanks for your help.

Use Excel VBA to Sort Excel Data

The following code runs when one of the rectangles is clicked. You can change the TopRow, iCol and strCol variables, to match the layout of your table.

Code

Sub SortTable()
  'code written by Dave Peterson 2005-10-22
  '2006-08-06 updated to accommodate hidden rows
  Dim myTable As Range
  Dim myColToSort As Long
  Dim curWks As Worksheet
  Dim mySortOrder As Long
  Dim FirstRow As Long
  Dim TopRow As Long
  Dim LastRow As Long
  Dim iCol As Integer
  Dim strCol As String
  Dim rng As Range
  Dim rngF As Range


  TopRow = 1
  iCol = 10  'number of columns in the table
  strCol = "A"  ' column to check for last row


  Set curWks = ActiveSheet


  With curWks
    LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
    If Not .AutoFilterMode Then
        Set rng = .Range(.Cells(TopRow, strCol), _
                .Cells(LastRow, strCol))
    Else
        Set rng = .AutoFilter.Range
    End If
    
    Set rngF = Nothing
    On Error Resume Next
    With rng
       'visible cells in first column of range
       Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
           .SpecialCells(xlCellTypeVisible)
    End With
    On Error GoTo 0
    
    If rngF Is Nothing Then
         MsgBox "No visible rows. Please try again."
         Exit Sub
    Else
         FirstRow = rngF(1).Row
    End If
        
    myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
    
    Set myTable = .Range("A" & TopRow & ":A" _
            & LastRow).Resize(, iCol)
    If .Cells(FirstRow, myColToSort).Value _
      < .Cells(LastRow, myColToSort).Value Then
        mySortOrder = xlDescending
    Else
        mySortOrder = xlAscending
    End If
    
    myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
              order1:=mySortOrder, _
              header:=xlYes
  End With


End Sub

Display More

parthasarathi · Oct 27th 2010