Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting ListBox results or transposing ListBox values to other cells for sorting
I have a table with a column of user selected List boxes, e.g.:
Listbox1 Listbox2 Listbox3 ListBox4 ListBox5 ListBox6 The listboxes can be used to select numbers from 1 to 6. Once these numbers are selected I want it to allow you to sort them in numerical asending order. A basic excel sort will not work as excel does not pick up the listbox values. To get round this I have created an adjacent hidden column- B-Column (whereas my listboxes are in A-column) and plan to transpose the list box value into this for sorting. I aim to do this with a command button that starts a macro. With my basic knowledge of VB I can do the first row with: Private Sub CommandButton1_Click() ActiveSheet.Range("B2") = ListBox1.Value - 1 End Sub But I can't get it to do the 2,3,4,5, & 6th rows of listboxes. Each ListBox is named based on its position (i.e. in 2nd row of data sits "ListBox2"). (I've used -1 cos output is always +1 what it should be.) How can I get this macro to loop down the B-column to display all listbox values? Then I can sort by the B column. Or is there a more basic way to simply sort a column of listboxes Any help much appreciated Regards Rob Hamilton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting ListBox results or transposing ListBox values to other cellsfor sorting
Do you need to use listboxes?
It kind of sounds like Data|validation would be sufficient. And you could sort those cells normally. Rob wrote: I have a table with a column of user selected List boxes, e.g.: Listbox1 Listbox2 Listbox3 ListBox4 ListBox5 ListBox6 The listboxes can be used to select numbers from 1 to 6. Once these numbers are selected I want it to allow you to sort them in numerical asending order. A basic excel sort will not work as excel does not pick up the listbox values. To get round this I have created an adjacent hidden column- B-Column (whereas my listboxes are in A-column) and plan to transpose the list box value into this for sorting. I aim to do this with a command button that starts a macro. With my basic knowledge of VB I can do the first row with: Private Sub CommandButton1_Click() ActiveSheet.Range("B2") = ListBox1.Value - 1 End Sub But I can't get it to do the 2,3,4,5, & 6th rows of listboxes. Each ListBox is named based on its position (i.e. in 2nd row of data sits "ListBox2"). (I've used -1 cos output is always +1 what it should be.) How can I get this macro to loop down the B-column to display all listbox values? Then I can sort by the B column. Or is there a more basic way to simply sort a column of listboxes Any help much appreciated Regards Rob Hamilton -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox Values | Excel Discussion (Misc queries) | |||
How do i set up a dropdown listbox of values in a column of cells | Excel Worksheet Functions | |||
Listbox 2 takes the value of Listbox 1 | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Listbox B if LIstbox A equals | Excel Discussion (Misc queries) |