Combo box on user form

[anon]

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.

 

[Joel]

See answers below

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
---------------------------------------------------------------------------------