Hi Jerry, Sorry for the late reply. I kinda figured a way to add in the cell referencing. Here is the complete macro if any one needs it for a reference. Thanks for all your help Jerry. Cheers.
Private Sub CheckBox1_Click()
Dim rList As Range
Dim sColLtr As String
Dim lColFirst As Long, lColLast As Long
Dim lRow As Long, i As Long
Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer
ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If
RowCount = Selection.Rows.Count
RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)
lColFirst = ColStart
lColLast = ColStart + ColCount - 1
'Sort List 1
If CheckBox1 = True Then 'has headers: Header Names
ComboBox1.Clear
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox1
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
ComboBox1.Clear
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox1.AddItem "Column " & sColLtr
Next i
End If
'Sort List 2
If CheckBox1 = True Then 'has headers: Header Names
ComboBox2.Clear
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox2
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
ComboBox2.Clear
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox2.AddItem "Column " & sColLtr
Next i
End If
'Sort List 3
If CheckBox1 = True Then 'has headers: Header Names
ComboBox3.Clear
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox3
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
ComboBox3.Clear
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox3.AddItem "Column " & sColLtr
Next i
End If
End Sub
Private Sub Userform_Initialize()
Dim rList As Range
Dim sColLtr As String
Dim lColFirst As Long, lColLast As Long
Dim lRow As Long, i As Long
Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer
ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If
RowCount = Selection.Rows.Count
RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)
lColFirst = ColStart
lColLast = ColStart + ColCount - 1
'Sort List 1
If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox1
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox1.AddItem "Column " & sColLtr
Next i
End If
'Sort List 2
If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox2
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox2.AddItem "Column " & sColLtr
Next i
End If
'Sort List 3
If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox3
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With
Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox3.AddItem "Column " & sColLtr
Next i
End If
End Sub
Private Sub CommandButton1_Click()
'Sort List 1
Dim order1op As XlSortOrder
Dim headerlop As XlYesNoGuess
Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer
Dim firstcolumn As Integer
Dim lastcolumn As Integer
ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If
RowCount = Selection.Rows.Count
RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)
firstcolumn = ColStart
lastcolumn = ColStart + ColCount - 1
If OptionButton1 Then
order1op = xlAscending
Else
order1op = xlDescending
End If
If OptionButton3 Then
order2op = xlAscending
Else
order2op = xlDescending
End If
If OptionButton5 Then
order3op = xlAscending
Else
order3op = xlDescending
End If
If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox1 Then
aCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If
Next i
headerlop = xlYes
Else
aCell = Right(ComboBox1, Len(ComboBox1) - InStr(1, ComboBox1, " "))
aCell = aCell & RowStart
headerlop = xlNo
End If
If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox2 Then
bCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If
Next i
headerlop = xlYes
Else
bCell = Right(ComboBox2, Len(ComboBox2) - InStr(1, ComboBox2, " "))
bCell = bCell & RowStart
headerlop = xlNo
End If
If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox3 Then
cCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If
Next i
headerlop = xlYes
Else
cCell = Right(ComboBox3, Len(ComboBox3) - InStr(1, ComboBox3, " "))
cCell = cCell & RowStart
headerlop = xlNo
End If
Set oneRange = Selection
If ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 <> "" And (OptionButton1 Or OptionButton2) _
And (OptionButton3 Or OptionButton4) And (OptionButton5 Or OptionButton6) Then
oneRange.Sort Key1:=Range(aCell), Key2:=Range(bCell), Key3:=Range(cCell), Order1:=order1op, Order2:=order2op, _
Order3:=order3op, Header:=headerlop
ElseIf ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 = "" And (OptionButton1 Or OptionButton2) _
And (OptionButton3 Or OptionButton4) Then
oneRange.Sort Key1:=Range(aCell), Key2:=Range(bCell), Order1:=order1op, Order2:=order2op, Header:=headerlop
ElseIf ComboBox1 <> "" And ComboBox2 = "" And ComboBox3 = "" And (OptionButton1 Or OptionButton2) Then
oneRange.Sort Key1:=Range(aCell), Order1:=order1op, Header:=headerlop
End If
UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub