Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box on user form
Hi,
I'm looking for my users to be able to do a 'sort' function on the activesheet. I have custom toolbars so will add a custom button 'sort'. This will open a custom userform (which I am intending to be similar to the form opened by clicking on sort on a std excel menu). I want the userform to have three combo boxes to select sort order - eg. sort by, then by, then by.... I can create the userform however am unsure on a few things; 1. How do I assign values to the combo boxes - I know how to do this via code but as the values will not change I can't see why I should use code. This is a stupid question but do not know how to do it via the properties of the combo box 2. Can I set the values selected by the user in the combo boxes to variables and then use these in my code to perform the sort? Is it a simple dim val1 as string set val1 = combobox1.value ?? 3. Can I add a further level to my user form where a user can choose to exclude certain rows of data from the sort? So if ticked these rows would be moved to the bottom of the selection and excluded from the sort area. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box on user form
See answers below
"anon" wrote: Hi, I'm looking for my users to be able to do a 'sort' function on the activesheet. I have custom toolbars so will add a custom button 'sort'. This will open a custom userform (which I am intending to be similar to the form opened by clicking on sort on a std excel menu). I want the userform to have three combo boxes to select sort order - eg. sort by, then by, then by.... I can create the userform however am unsure on a few things; 1. How do I assign values to the combo boxes - I know how to do this via code but as the values will not change I can't see why I should use code. This is a stupid question but do not know how to do it via the properties of the combo box ------------------------------------------------------------------------------ Answer: I is probably easier and less error prone for code to enter the data initially. This can be a seperate macro which gets run once and not part of the final code. ------------------------------------------------------------------------------- 2. Can I set the values selected by the user in the combo boxes to variables and then use these in my code to perform the sort? Is it a simple dim val1 as string set val1 = combobox1.value ?? -------------------------------------------------------------------------------- Answer: Yes. You don't even need the set. just val1 = combobox1.value -------------------------------------------------------------------------------- 3. Can I add a further level to my user form where a user can choose to exclude certain rows of data from the sort? So if ticked these rows would be moved to the bottom of the selection and excluded from the sort area. --------------------------------------------------------------------------------- Answer: Why don't you just move the excluded rows to the end of the worksheet before the sort. Then set the sort range to exclude the these rows and the end of the sort range. lastrow = cells(rows.count,"A").end(xlup).row rows("2:5").cut destination:=rows(LastRow + 1) 'excluded row count is 5-2+1=4 'therefore sort range is 1 to 6 (10 - 4) Range("A1:F6").sort 'then the rest of the sort statement --------------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box on User Form Question | Excel Programming | |||
combo box in a user form | Excel Programming | |||
Can't seem to get Combo Box on User Form | Excel Programming | |||
Currency format for combo box in a user form | Excel Programming | |||
Patrick -- 424 Combo Box User Form | Excel Programming |