UserForm to Sort Worksheet

Greetings & Salutations,


I am trying to put in some sort options. I have created a user form that has 5 sort options.
Color of the cells.
Part Name
Part Number
Wheather the part was even ordered (Yes/No) (Would like it sorted with Yes on top, so Decending).
Quantity ordered. (Also Decending).


Below is a sample of my first attempt (just sorting by quantity so far). However, I'm getting an error when I test the code.
"Compile Error: Object Required" and it's highlighting the "Set lngRow = Range("A" & Rows.Count).End(xlUp).Row".


Any help getting any one of these sort options to work would be great. I should be able to figure out the rest if I have one that works.


Thanks.

Code:

---

Private Sub btnSort_Click()
'Determines which sort option is selected and calls the correct sub.


    'Declare the variables.
    Dim nSheets
    Dim lngRow As Long
    'Dim lngRng As Range
   
'    'Sort Importance
'    If OptionColor Then Call SortImportance
'    End If
'
'    'Sort Part Name
'    If OptionName Then Call SortPartName
'    End If
'
'    'Sort Part Number
'    If OptionNumber Then Call SortPartNumber
'    End If
'
'    'Sort Ordered (Yes)
'    If OptionOrdered Then Call SortPartOrdered
'    End If
   
    'Sort Quantity
        'Sorts the active sheet by the quantity ordered.
    If OptionQuantity Then


    'Declare the array of sheets to ignore.
    nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList", "Master Parts List")
   
        'Check the active sheet to make sure it is not in the array.
        With ActiveWorkbook
            If ActiveSheet = nSheets Then Exit Sub
                'Determine the number of rows.
                With ActiveSheet
                    Set lngRow = Range("A" & Rows.Count).End(xlUp).Row
               
                    'Apply the sort.
                    With .Sort
                        .SortFields.Clear
                        .SortFields.Add Key:=lngRow.Columns("E"), SortOn:=xlSortOnValues, Order:=xlDecending, DataOption:=xlSortNormal
                        .SetRange lngRow
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                End With
            End If
        End With
    End If
End Sub

---

Code:

---

with ActiveSheet
  .cells(1).currentregion.sort .cells(1,5),2,,,,,,1
end With

---

Quote:

---

Originally Posted by snb

Code:

---

with ActiveSheet
  .cells(1).currentregion.sort .cells(1,5),2,,,,,,1
end With

---

---

snb,

Thanks for the quick response, but I'm lost. What do I do with this?

I'm assuming I replace the current With ActiveSheet section, but what do I don't know what ".Cells(1, 5), 2, , , , , , 1" means.

The keyword "Set" is only used with object variables, so the code is telling VBA that lngRow is an Object.
with other Variable Types, you don;t need any special keyword

Code:

---

  lngRow = Range("A" & Rows.Count).End(xlUp).Row

---

You can use the Keyword "Let", but is was only kept around for Olde Programmers who had it engrained in their muscle memory.

Code:

---

Let lngRow = Range("A" & Rows.Count).End(xlUp).Row

---

When you get into more advance VBA coding you will see that "Let" is still used when writing custom Object Properties. See "Set, Let, Get" in the VBA help file. Or is it "Get, Let, Set?"

This will be the next error you run into

Code:

---

.SortFields.Add Key:=lngRow.Columns("E")

---

Since lngRow is a number... "42.Columns("E")" is nonsense. You might try using

Code:

---

Cells(lngRow, "E")

---

Have fun with the Sort by color :devil2:

Totally guessing about your workbook structure, check out the structure and lay out of this code. All I can say is that it Compiles as far as I've written it.

Code:

---

Option Explicit

'Declare Module wide Variables
'These variables can be used in any routine in this UserForm
Private nSheets As Variant 'Sheets to ignore

---

Code:

---

Private Sub UserForm_Initialize()
'Intialize Module wide Variables
nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList", "Master Parts List")

'Intialize other things
'
'
'
End Sub

---

Code:

---

Private Sub btnSort_Click()
'Since you have a choice of multiple actions to perform,
'  this sub only decides the action to take

    Dim i As Long

    For i = LBound(nSheets) To UBound(nSheets)
      If ActiveSheet.Name = nSheets(i) Then Exit Sub
    Next i
   
    If Me.Controls("OptionQuantity") Then QuantitySort
    If Me.Controls("OptionPartName") Then PartNameSort
    If Me.Controls("OptionPartNum") Then PartNumSort
    If Me.Controls("OptionOrdered") Then PartOrderedSort
 

End Sub

---

Code:

---

Private Sub QuantitySort()
'I am guessing that the table starts in A1 and the headers are in Row 1.
' That the table is surrounded by empty cells.

 ActiveSheet.Range("A1").CurrentRegion.Sort _
              Key1:=Range("E1"), _
              Order1:=xlDescending, _
              Header:=xlYes
              'MatchCase is ommitted 'cuz quantities are numerical, which have no case.
 End Sub

---

Code:

---

Private Sub PartNameSort()
'for you to do
End Sub

---

You can actually move all non-Event Subs, (Control_Click, Control_Change, etc,) to a separate Module, (Ie modTableSorts) and calling them by prefixing the Sub name with the Module Name and a Dot. If in a separate Module, do NOT use the Private Keyword when declaring them .

Code:

---

    If Me.Controls("OptionQuantity") Then modTableSorts.QuantitySort

---

This practice helps keep the code in the UserForm smaller and cleaner. It also makes those syubs available to any other UserForms that mght use them.

Quote:

---

Originally Posted by SamT

You can actually move all non-Event Subs, (Control_Click, Control_Change, etc,) to a separate Module, (Ie modTableSorts) and calling them by prefixing the Sub name with the Module Name and a Dot. If in a separate Module, do NOT use the Private Keyword when declaring them .
If Me.Controls("OptionQuantity") Then modTableSorts.QuantitySort

Formatting tags added by mark007

This practice helps keep the code in the UserForm smaller and cleaner. It also makes those syubs available to any other UserForms that mght use them.

---

SamT,

Thanks, this is proving most helpful. I did put the sort codes in a separate workbook level module (not the ThisWorkbook one). I have been able to use that same one code for both the UserForm and for the Clear Functions I wrote to reset each sheet to the default settings. Which I put in to allow the user to clear either a sheet or the entire workbook. This way I don't have to rely on them remembering to create a copy so they have a fresh one.

Quote:

---

Originally Posted by SamT

Have fun with the Sort by color :devil2:

---

I'm assuming you have had some experience with sorting by color.

You wouldn't happen to be able to help with that too, would you?

Either way, thanks again.

In Excel 2003, you must use a helper column, but in 2007 and higher, Sort By Color is available.

Use the Sort And Filter Menu to Custom Sort a range while Recording a Macro to see how to code it.