Problems Sorting After Changes are Made

[coliervile]

I'm having problem figuring how to sort (code) the worksheet "Leave Request" and update the listbox1 in the userform "frmRequest" when changes have been made using the "Make Changes" command button on the userform "frmRequest". The data in both the worksheet "Leave Request" and listbox1 on "frmRequest" does update, but I cant get the sort part figured out. I want the sort on the worksheet "Leave Request" as follows:

(this is an example not the actual coding, but I did try it)

Worksheets("Leave Request")
.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

then after the sort is done update the "frmRequest"- listbox1.


Best regards,

Charlie

[VBA]Private Sub CommandButton6_Click()
With frmRequest.ListBox1
'Check for selected item
If (.Value <> vbNullString) Then

Range(.RowSource)(.ListIndex + 1, 1).Value = frmRequest.TextBox1.Value
Range(.RowSource)(.ListIndex + 1, 2).Value = frmRequest.TextBox2.Value
Range(.RowSource)(.ListIndex + 1, 3).Value = frmRequest.TextBox3.Value
Range(.RowSource)(.ListIndex + 1, 4).Value = frmRequest.TextBox4.Value
Range(.RowSource)(.ListIndex + 1, 5).Value = frmRequest.TextBox5.Value

Else
MsgBox "Please Enter Data"

End If

End With

End Sub[/VBA]

[lucas]

just got a minute but it looks like you need a listbox change to update your change textboxes too....try this for your problem though....I'm not completely sure I understand.

[VBA]Private Sub CommandButton6_Click()
With frmRequest.ListBox1
'Check for selected item
If (.Value <> vbNullString) Then

Range(.RowSource)(.ListIndex + 1, 1).Value = frmRequest.TextBox1.Value
Range(.RowSource)(.ListIndex + 1, 2).Value = frmRequest.TextBox2.Value
Range(.RowSource)(.ListIndex + 1, 3).Value = frmRequest.TextBox3.Value
Range(.RowSource)(.ListIndex + 1, 4).Value = frmRequest.TextBox4.Value
Range(.RowSource)(.ListIndex + 1, 5).Value = frmRequest.TextBox5.Value
UserForm_Initialize
Else
MsgBox "Please Enter Data"

End If

End With

End Sub
[/VBA]

[mdmackillop]

Hi Charlie,
Unless there is a good reason to have the worksheet and listbox in the same order, I would upload the data into an array, sort the array(Quicksort or BubbleSort) and populate the listbox with the sorted data.

[coliervile]

Thanks guys for replying. Mr. mdmackillop do you have an example of what you're referrring to? Lucas I'll take a look at yours a bit later...got to go the planes are holding in the air waiting for me...LOL.

Best regards,

Charlie