sort by selected combo box value

Hi:

I have two Combo Boxes that are populated with column names of a table. The user should be able to pick columns names from these two boxes and the table will be sorted in the order of column name picked from the first box and then the 2nd. How do I do this? Thank you.
sdc248Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Calvin BrineCommented:
Something along these lines should work, assume your headers are labelled and don't exist in the actual data.

Dim ws As Worksheet
Set ws = ActiveSheet
ws.UsedRange.Sort ws.UsedRange.Find(UserForm1.ComboBox1), xlAscending, Header:=xlYes

HTH
Cal
0
Calvin BrineCommented:
Whoops, missed the second combo box thing.

Dim ws As Worksheet
Set ws = ActiveSheet
ws.UsedRange.Sort ws.UsedRange.Find(UserForm1.ComboBox1), xlAscending, ws.usedrange.find(Userform1.combobox2),xlascending, Header:=xlYes
0
soco180Commented:
Is this table a table in a database that you plan on querying while applying this Order By clause?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Calvin BrineCommented:
OK,
I promise this is the last one.  The other one had a problem if the headers names were similar.

Dim ws As Worksheet
Set ws = ActiveSheet
ws.UsedRange.Sort key1:=ws.UsedRange.Find(UserForm1.ComboBox1, , , xlWhole), order1:=xlAscending, Key2:=ws.UsedRange.Find(UserForm1.ComboBox2, , , xlWhole), order2:=xlAscending, Header:=xlYes
HTH
Cal
0
quiklearnerCommented:
add to the query " order by " & combo1.text & iif(combo2.listindex > -1, ", " & combo2.text,"")
0
soco180Commented:
Private Function BuildOrderBy() As String
'This assumes you want to ascedning order by
Dim s1 As String, s2 As String, sRet As String
    s1 = Trim(Combo1.Text)
    s2 = Trim(Combo2.Text)
    If Len(s1) > 0 Then
        sRet = "Order By " & s1 & " ASC"
    End If
    If (Len(s2) > 0) And (s1 <> s2) Then
        sRet = sRet & ", " & s2 & " ASC"
    End If
    BuildOrderBy = sRet
End Function
0
Calvin BrineCommented:
soco180,
  I encountered this questions in the excel forum, so I assumed that the question is in relation to excel.  This the first time I've been posting since the board change, but it looks like they can post to multiple forum's just to confuse things even more.

cal
0
quiklearnerCommented:
yep its actually in the VB area..  You could be right, we could be right...
0
soco180Commented:
'There was a slight hole in my original logic...

Private Function BuildOrderBy() As String
'This assumes you want to ascedning order by
Dim s1 As String, s2 As String, sRet As String
    s1 = Trim(Combo1.Text)
    s2 = Trim(Combo2.Text)
    If Len(s1) > 0 Then
        sRet = "Order By " & s1 & " ASC"
    End If
    If (Len(s2) > 0) And (s1 <> s2) Then
        If Len(sRet) > 0 Then
            sRet = sRet & ", " & s2 & " ASC"
        Else
            'Combo1 was blank
            sRet = "Order By " & s2 & " ASC"
        End If
    End If
    BuildOrderBy = sRet
End Function
0
soco180Commented:
CBrine...That explains it. I was wondering why you were writing for excel. LOL
0
Calvin BrineCommented:
Same thing for me, thought I ended up the Access forum by mistake...Still learning my way around the new board setup...It would be nice if the original message listed the forums it was posted to.  Would help determine what the poster is looking for.
0
sdc248Author Commented:
Hi guys. Sorry for the confusion. What I am working on is VBA code. There's no VBA to choose from the forum list so I chose Excel and VB.

The table I am talking about is located on an excel worksheet. Other than the Sort by/Then by combo boxes I also have some check boxes and text boxes and a command button on the same worksheet. When the user hit the command button, some calculations will be done and the table on the worksheet will be populated and sorted by whatever is choosen from the Sort by/Then by combo boxes.

So I suppose Cbrine's solution suits my needs better?
0
quiklearnerCommented:
i would believe so...
0
soco180Commented:
I also expect so...Give it a go.
0
Calvin BrineCommented:
sdc,
  You have the controls on the actual worksheet, not in a form.  That means I will need to make some changes.  Make sure you change the ComboBox names to the ones you are using.

Dim ws As Worksheet
Set ws = ActiveSheet
ws.UsedRange.Sort key1:=ws.UsedRange.Find(ComboBox1, , , xlWhole), order1:=xlAscending, Key2:=ws.UsedRange.Find(ComboBox2, , , xlWhole), order2:=xlAscending, Header:=xlYes
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdc248Author Commented:
Thanks Cbrine. There's a twist though. The table head of the table in question has 3 rows that look like:
     section1           section 2             section        'these are merged cells'
col1 col2 col3     col4 col5 col6   col7 col8 col9   'column names
str1 str2 str3      str4 str5 str6    str7 str8 str9    'some descriptions

Your code seems to be working except that it's looking at the first row and giving me an error message saying 'This operation requires the merged cells to be identically sized."...
0
Calvin BrineCommented:
sdc,
  I would suggest a real range reference instead of usedrange.  I'm assuming your sort headers are in row 2?

Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("A2",ws.range("I"&application.rows.count).end(xlup)).Sort key1:=ws.UsedRange.Find(ComboBox1, , , xlWhole), order1:=xlAscending, Key2:=ws.UsedRange.Find(ComboBox2, , , xlWhole), order2:=xlAscending, Header:=xlYes
0
Calvin BrineCommented:
PS-My code assumes that Column I will have data in all rows that the others do.  So if your last cell in column A is 90 and your last one in I is 85, it will pick the wrong range.  If all columns of data ends at row 90, then you are OK.  If this is not the case then I would suggest you use a column that you know will always be populated for all rows, and then offset from there .
ex. Assuming column "H" will always be populated.
ws.Range("A2",ws.range("H"&application.rows.count).end(xlup).offset(0,1))

Cal
0
sdc248Author Commented:
Thanks Cbrine. Actually it's your latest piece of code works. I clicked on a previous version to be "acepted solution" but I guess that's alright. :>
0
Calvin BrineCommented:
sdc,
  Glad I could help.  The first code will work as a solution to do the same thing, just from a user form instead of the worksheet controls.  So, it is a solution, just not one for you:-).

Cal
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.