Sort order via excel userform combobox

[jeff.white]

I have a similar problem with 4 comboboxes on an Excel Userform. Each
comobox has a list from column A, B, C and D. Column A is the
original Sort order by on the worksheet. What I'd like to see is when
I select Combobox2 (which is set for column B) that the order is
ascending, then if I were to switch to Combobox3, set up for column C
that Column C is now sorted in ascending order. If I go back to
Combobox1, which is Column A, this is again sorted. I've worked
through some code, but I get errors as I make a selection from the
list. Thanks!

 

[Dave Peterson]

It sounds like you have one column for each combobox. Is there a reason why you
just can't sort just column A in ascending order, then sort just column B, then
sort just column C, then sort just column D.

There won't be any relationship for each of these rows, though.

 

[jeff.white]

It sounds like you have one column for each combobox. Is there a reason why you
just can't sort just column A in ascending order, then sort just column B, then
sort just column C, then sort just column D.

There won't be any relationship for each of these rows, though.

Dave...I don't think that would work. I the four comboboxes, based on
what the value is, populates various textboxes. Here is some sample
code that I have so far:

Private Sub ComboBox1_Change()
Dim EmpID
Dim Rng As Range
Dim ws1 As Worksheet
Dim NumRows As Long
NumRows = Worksheets("n11").Cells(Rows.Count, "A").End(xlUp).Row - 1

EmpID = CLng(ComboBox1.Value)
Set ws1 = Worksheets("n11")
Set Rng = ws1.Range("c$1:x$1600") ' This column has Employee ID
numbers

If ComboBox1.Value <> "" Then
TextBox1.Value = Application.VLookup(EmpID, Rng, 2, 0)
TextBox2.Value = Application.VLookup(EmpID, Rng, 3, 0)
TextBox3.Value = Application.VLookup(EmpID, Rng, 4, 0)....onto
Textbox20....

Combobox2 would have similar settings only Rng would be set to Column
D - X.

That is why I was thinking that if the sort option can be initiated as
each combobox is selected it would make the list alot more user
friendly. This is a great user group and I've gotten assistance from
you in the past as well as others so if you have any other
suggestions, that would be great. I tried recording a macro that
sorted column A (for example) and copied that bit of code in the front
of the above code

Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

but I got a debug error (Run time error 1004 - Sort Method of Range
Class Failed). When I cleared that and reselected the combobox the
list was now in order. Thanks for your thoughts on this...

 

[Dave Peterson]

I'm not sure I would make it like that.

Have you thought of adding a combobox/listbox that gives the user a list of what
fields you want them to be able to sort on.

Then you could have one combobox that displays the data based on that selection.

But maybe this will give you an idea if you still want to use multiple
comboboxes.

I put 4 comboboxes on a userform (combobox1, ..., combobox4. The names are
important.)

I put the data in a worksheet named N11 in A124. Combobox1 controlled column
1. Combobox2 controlled column 2. Combobox# would control column #.

This is the code I used behind the userform:

Option Explicit
Dim myRng As Range
Private Sub ComboBox1_Enter()
Call ResetComboboxes(ComboBox1)
End Sub
Private Sub ComboBox2_Enter()
Call ResetComboboxes(ComboBox2)
End Sub
Private Sub ComboBox3_Enter()
Call ResetComboboxes(ComboBox3)
End Sub
Private Sub ComboBox4_Enter()
Call ResetComboboxes(ComboBox4)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Worksheets("n11")
Set myRng = .Range("a1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

For iCtr = 1 To 4
Me.Controls("Combobox" & iCtr).ColumnCount = 4
Next iCtr
End Sub
Sub ResetComboboxes(WhichCombobox As msforms.ComboBox)

Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.ComboBox Then
ctrl.RowSource = ""
End If
Next ctrl

With myRng
.Cells.Sort key1:=.Columns(CLng(Right(WhichCombobox.Name, 1))), _
order1:=xlAscending, header:=xlYes
End With

WhichCombobox.RowSource = myRng.Address(external:=True)

End Sub