Solved

sort by selected combo box value

Posted on 2007-04-10
20
217 Views
Last Modified: 2010-04-30
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.
0
Comment
Question by:sdc248
  • 9
  • 5
  • 3
  • +1
20 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884056
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884068
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
 
LVL 2

Expert Comment

by:soco180
ID: 18884111
Is this table a table in a database that you plan on querying while applying this Order By clause?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884132
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
 
LVL 4

Expert Comment

by:quiklearner
ID: 18884135
add to the query " order by " & combo1.text & iif(combo2.listindex > -1, ", " & combo2.text,"")
0
 
LVL 2

Expert Comment

by:soco180
ID: 18884145
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884276
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
 
LVL 4

Expert Comment

by:quiklearner
ID: 18884295
yep its actually in the VB area..  You could be right, we could be right...
0
 
LVL 2

Expert Comment

by:soco180
ID: 18884308
'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
 
LVL 2

Expert Comment

by:soco180
ID: 18884325
CBrine...That explains it. I was wondering why you were writing for excel. LOL
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884359
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
 

Author Comment

by:sdc248
ID: 18884386
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
 
LVL 4

Expert Comment

by:quiklearner
ID: 18884411
i would believe so...
0
 
LVL 2

Expert Comment

by:soco180
ID: 18884454
I also expect so...Give it a go.
0
 
LVL 16

Accepted Solution

by:
Calvin Brine earned 125 total points
ID: 18884599
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
 

Author Comment

by:sdc248
ID: 18884722
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884792
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18884820
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
 

Author Comment

by:sdc248
ID: 18893406
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 18912355
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question