+ Reply to Thread
Results 1 to 2 of 2

Script/Macro to Sort Protected Cells with AutoFilters

  1. #1
    Registered User
    Join Date
    09-24-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Script/Macro to Sort Protected Cells with AutoFilters

    Hello. I'm new to this forum, but an avid power Excel user for many years. However, I'm not very adept with VBA syntax...though I did figure out how to automate autofiltering when a workbook is first opened using the Activate and ApplyFilter commands. What I could really use some help with is a script that will allow us to sort locked/protected/autofiltered cells (a well documented challenge) containing formulas that are linked to data in an external "master" spreadsheet. In other words, prevent users from being able to edit cells with dynamic formulas, yet still allow them to sort the autofiltered results.

    I have searched high and low and can't quite find enough info to build this script that's needed on my own. I have come up with a couple sets of code that come close, but lack the combination of: 1. working with an autofiltered data set (vs whole columns of data in large 50MB+ files) AND 2. being "portable" enough to be invoked on the active column header (vs having to use a discrete range for every column header in every worksheet). Here's what I have so far on those two fronts for ascending sorts (descending will be simple enough once this is worked out, though it would be nice to have a single routine that does both alternatively by, say, repeatedly clicking on the column header assigned to a macro).

    1. code that works with the active cell column, but without regard to the autofilter ranges:

    Sub SortAsc()

    ActiveSheet.Unprotect
    ActiveCell.EntireColumn.Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True
    End Sub

    2. code that works with the autofilter ranges, but would require a separate subroutine for every column header in every worksheet (since the range of rows varies from worksheet to worksheet):

    Sub SortAsc()

    ActiveSheet.Unprotect
    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("B1:B15000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True
    End Sub

    Can someone please help me merge these two partial solutions together to come up with a macro/script that can be used to sort with the autofilters for any column in any worksheet in the active workbook? I think I'm really close, but just couldn't find enough info on the syntax AutoFilter.Sort/Selection.Sort objects.

    Thanks!
    Last edited by cabenjam; 09-25-2012 at 12:34 AM.

  2. #2
    Registered User
    Join Date
    09-24-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Script/Macro to Sort Protected Cells with AutoFilters

    BTW, what would be really cool is if there's a way to have the macro/script turn off protection, let the user use the built in autofilter sort ascending/descending functionality and then turn protection back on. That would allow the column header to display the little sort direction arrow indicating to the user what column the data is being sorted on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1