Solved

sort by selected combo box value

Posted on 2007-04-10
20
209 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now