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

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