Prompt User To Sort Data IF required

[Mister H]

Hi All:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am wondering if it is possible using VBA to ask a user if they would like to sort the data on a worksheet (titled Master) and if so allow them to select the sorting method?<o></o>
<o></o>
I have a macro I am using that compiles all the workbook in a folder. After the data i compiled I =would like to ask the user if they want to sort the data. If they choose yes then show them the column heading in Row 1 and allow them to select how they wish to sort their data. If they choose not to sort it then just exit the sub.<o></o>


Can someone tell me if this is possible and if so get me pointed in the right direction? I searched here and Google but have not yet come across an answer.

THANKS,
Mark

 

[VoG]

Hi Mark.

Maybe like this

Sub sorttest()
If MsgBox("Do you want to sort", vbQuestion + vbYesNo) = vbNo Then Exit Sub
Application.Dialogs(xlDialogSort).Show
End Sub

 

[Mister H]

THANKS VoG YOUR EXPERTISE is always appreciated

Is there a way to get it to default to Header Row as opposed to No Header Row?

Other then that this is exactly what I was looking for.

Take Care,
Mark

 

[Mister H]

Hi VoG:

I came up with this but I am uncertain if there is any negative to using it as I do not unedrstand it

Application.Dialogs(xlDialogSort).Show arg1:=xlTopToBottom, arg8:=xlYes

Also, I put in Cells.Select so it selects all the data. It seemed to have a problem if there was a blank row in the data. I don't think there would be a blank row but Just In Case

Sub SortOption()
'Call up the Sort Option at the users discretion
Cells.Select
If MsgBox("Do you want to sort the data that has been compiled?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
 
'No Column Headers
'Application.Dialogs(xlDialogSort).Show
 
'with Column Headers
Application.Dialogs(xlDialogSort).Show arg1:=xlTopToBottom, arg8:=xlYes 
 
End Sub

ALso just in case anyone reuires the same here is the code I am using that compiles all the data from the Excel files contained in the selected folder:

Sub Consolidate()
'Author:     Jerry Beaucaire'
'Date:       9/15/2009     (2007 compatible)  (updated 4/29/2011)
'Summary:    Merge files in a specific folder into one master sheet (stacked)
'            Moves imported files into another folder
Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet
'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now
 
    Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into
With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .Cells.Clear
        NR = 1
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If
'Path and filename (edit this section to suit)
    'fPath = "C:\2011\Files\"            'remember final \ in this string
    MsgBox "Please select a folder with files to consolidate"
    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                fPath = .SelectedItems(1) & "\"
                Exit Do
            Else
                If MsgBox("No folder chose, do you wish to abort?", _
                    vbYesNo) = vbYes Then Exit Sub
            End If
        End With
    Loop
 
 
 
    fPathDone = fPath & "Imported\"     'remember final \ in this string
    On Error Resume Next
        MkDir fPathDone                 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.xls*")        'listing of desired files, edit filter as desired
'Import a sheet from found files
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then              'don't reopen this file accidentally
            Set wbData = Workbooks.Open(fPath & fName)  'Open file
        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            If NR = 1 Then                              'copy the data AND titles
                Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
            Else                                        'copy the data only
                Range("A2:A" & LR).EntireRow.Copy .Range("A" & NR)
            End If
 
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
            fName = Dir                                       'ready next filename
        End If
    Loop
End With
ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub

Have a GREAT day ALL,
Mark

 

[jbeaucaire]

Hat-tip to MisterH, glad to see these macros getting used!