Excel VBA (userform) about ListBox

Question

Is it possible to use a listbox to change the order of rows in a worksheet? I have searched the Web for about an hour without ressults. I am using the following code for the task:

 Private Sub UserForm_Initialize()
 ListBox1.RowSource = "Sheet1!A1:A15"
 End Sub

 Option Explicit 
 Const UP As Integer = -1 
 Const DOWN As Integer = 1 

 Private Sub SpinButton1_SpinUp() 

If Me.ListBox1.ListCount > 1 Then 
    Call MoveListItem(UP, Me.ListBox1) 
End If 

End Sub 

Private Sub SpinButton1_SpinDown() 

If Me.ListBox1.ListCount > 1 Then 
    Call MoveListItem(DOWN, Me.ListBox1) 
End If 

End Sub 

Private Sub MoveListItem(ByVal intDirection As Integer, _ 
ByRef ListBox1 As ListBox) 

Dim intNewPosition As Integer 
Dim strTemp As String 

intNewPosition = ListBox1.ListIndex + intDirection 
strTemp = ListBox1.List(intNewPosition) 

If intNewPosition > -1 _ 
And intNewPosition < ListBox1.ListCount Then 

     'Swap listbox items
    ListBox1.List(intNewPosition) = ListBox1.Value 
    ListBox1.List(intNewPosition - intDirection) = strTemp 
    ListBox1.Selected(intNewPosition) = True 

End If 

End Sub

Hope somone can give me a hint!

UPDATE!!

What i want is, if I for example have these values in a column in my worksheet:

week1

week2

week3

week4

Then I would like to re-arrenge the order of these values with the SpinButton in the ListBox;

week2

week4

week1

week3

What do you mean change order of rows? sorting? can you provide a before and after of your data? that would help us know what you are looking to do. — guitarthrower
– guitarthrower, Commented Oct 2, 2014 at 17:03
@guitarthrower I have updated my question, hope you kan help! — janus Mack
– janus Mack, Commented Oct 2, 2014 at 17:12

BradyK · Accepted Answer · 2014-10-02 17:27:12Z

1

You most certainly can do that!

Here is a quick code that does this in general, I will leave it to you to place this where it is needed:

For i = 0 To ListBox1.ListCount - 1
    ActiveWorkbook.Sheets("Sheet1").Range("A" & CStr(i + 1)).Value = ListBox1.List(i)
Next i

You'll probably need to change what is inside the for loop to better reflect your own code. For writing to a specific range just add whatever starting row number you want!

answered Oct 2, 2014 at 17:27

BradyK

3871 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA (userform) about ListBox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related