Sorting ListBox results or transposing ListBox values to other cells for sorting

[Rob]

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]

Do you need to use listboxes?

It kind of sounds like Data|validation would be sufficient. And you could sort
those cells normally.