How to use the args in the dialog(xldialogsort).show method


  • I have an Excel 2010 workbook with extensive macro automation. A large part of the in use area of the sheets are locked to control where the user can enter data. I have a main table area where the user enters a type of transaction data in rows of the table. There's a column that is managed by the macro code that is locked. I'm trying to implement a macro that will allow the user to sort rows of the table and include that locked column. I know that the normal sort function does not allow the user to sort locked columns.

    I've been investigating using the Application.Dialogs(xlDialogSort).Show method to display the Sort built-in dialog to enable a user to sort the rows. I'm very close to getting it working, but I want to understand how to use the args available. I've looked around the MSDN site for documentation and examples for this but haven't found what I'm looking for.

    The argument lists for the sort dialogs are:
    xlDialogSort     orientation, key1, order1, key2, order2, key3, order3, header, custom, case
    xlDialogSort     orientation, key1, order1, type, custom
    xlDialogSortSpecial     sort_by, method, key1, order1, key2, order2, key3, order3, header, order, case

    Does anyone know if there is more detailed information on the usages of these parameters? For example, in the code below, I would like to pass the user's column selection to the Sort by field of the dialog.

    The essence of the code. There is more logic where I only allow sorts in certain areas. This works, but the values in the dialog are remembered from one invocation to the next.

    Sub wsSortRows()

    Dim PrevRange As Range
    Dim rngSortRows As Range
    Dim diagAnswer As Boolean

        ' Save the user's selection.
        Set PrevRange = Selection
        ' Expand the selected range to include the locked columns in the table.
        ' <Leftmost>.column and <rightmost>.column are derived from Named ranges.

        Set rngSortRows = Range(Cells(Selection.Row, <leftmost>.Column), _
                                Cells(Selection.Row + Selection.Rows.count - 1,<rightmost>.column)
        diagAnswer = Application.Dialogs(xlDialogSort).Show ' I want to pass args here.

        ' Restore the user's selection.

    End Sub

    Thursday, May 10, 2012 9:26 AM


All replies