Being new to programmatically populating a listbox I have written code below. I am using a list box with 3 columns and the code below writes information into an array and then passes the array on to the listbox1.list.
So far so good. The thing is that I don't know how to update the current list in the listbox. The listbox updates only when I manually move the scrollbar of the listbox up and down.
So my question is how can I get the new updated list displayed in my listbox.
Any advise welcome.
Thanks
Jens
For i = 1 To 336
Application.StatusBar = "Update Results List: " & i
dummy = Sheets("3d rotate").Cells(7 + i, 6)
If dummy <> "" Then
j = j + 1
Sheets("3d rotate").Cells(10999 + j, 1) = dummy 'name
Sheets("3d rotate").Cells(10999 + j, 2) = Round(Sheets("3d rotate").Cells(7 + i, 27), 1) 'percent inhibition
Sheets("3d rotate").Cells(10999 + j, 3) = i 'record
Sheets("3d rotate").Cells(10999 + j, 4) = Round(Sheets("3d rotate").Cells(7 + i, 34), 1) 'cv
End If
my_array(i, 0) = ""
my_array(i, 1) = ""
my_array(i, 2) = ""
Next
Sheets("Results").ListBox1.List = my_array
'sort data according to % Inhibition
'don't try to sort the field if no hits were returned
If j > 0 Then
If alpha = 1 Then
Sheets("3d rotate").Select
Sheets("3d rotate").Cells(11000, 1).Resize(j, 4).Select
Selection.Sort Key1:=Cells(11000, alpha), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
Sheets("3d rotate").Select
Sheets("3d rotate").Cells(11000, 1).Resize(j, 3).Select
Selection.Sort Key1:=Cells(11000, alpha), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End If
For i = 1 To j
dummy = Sheets("3d rotate").Cells(10999 + i, 1)
my_array(i, 0) = dummy
my_array(i, 1) = CStr(Round((Sheets("3d rotate").Cells(10999 + i, 2)), 0)) & " %"
my_array(i, 2) = CStr(Round(Sheets("3d rotate").Cells(10999 + i, 4), 0)) & " %"
Next
Sheets("Results").ListBox1.List = my_array
So far so good. The thing is that I don't know how to update the current list in the listbox. The listbox updates only when I manually move the scrollbar of the listbox up and down.
So my question is how can I get the new updated list displayed in my listbox.
Any advise welcome.
Thanks
Jens
For i = 1 To 336
Application.StatusBar = "Update Results List: " & i
dummy = Sheets("3d rotate").Cells(7 + i, 6)
If dummy <> "" Then
j = j + 1
Sheets("3d rotate").Cells(10999 + j, 1) = dummy 'name
Sheets("3d rotate").Cells(10999 + j, 2) = Round(Sheets("3d rotate").Cells(7 + i, 27), 1) 'percent inhibition
Sheets("3d rotate").Cells(10999 + j, 3) = i 'record
Sheets("3d rotate").Cells(10999 + j, 4) = Round(Sheets("3d rotate").Cells(7 + i, 34), 1) 'cv
End If
my_array(i, 0) = ""
my_array(i, 1) = ""
my_array(i, 2) = ""
Next
Sheets("Results").ListBox1.List = my_array
'sort data according to % Inhibition
'don't try to sort the field if no hits were returned
If j > 0 Then
If alpha = 1 Then
Sheets("3d rotate").Select
Sheets("3d rotate").Cells(11000, 1).Resize(j, 4).Select
Selection.Sort Key1:=Cells(11000, alpha), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
Sheets("3d rotate").Select
Sheets("3d rotate").Cells(11000, 1).Resize(j, 3).Select
Selection.Sort Key1:=Cells(11000, alpha), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End If
For i = 1 To j
dummy = Sheets("3d rotate").Cells(10999 + i, 1)
my_array(i, 0) = dummy
my_array(i, 1) = CStr(Round((Sheets("3d rotate").Cells(10999 + i, 2)), 0)) & " %"
my_array(i, 2) = CStr(Round(Sheets("3d rotate").Cells(10999 + i, 4), 0)) & " %"
Next
Sheets("Results").ListBox1.List = my_array