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
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.
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
.SortFields.Add Key:=lngRow.Columns("E")
Since lngRow is a number... "42.Columns("E")" is nonsense. You might try using
Have fun with the Sort by color
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.
Option Explicit
'Declare Module wide Variables
'These variables can be used in any routine in this UserForm
Private nSheets As Variant 'Sheets to ignore
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
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
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
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 .
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.