How can I update a listbox 1

[JensKKK]

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

 

[Chance1234]

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

Is the data being added to the listbox ok ?



Chance,

Filmmaker, gentleman and ROMAN!

 

[JensKKK]

Yes the data look fine once I move the scrollbar.

 

[Chance1234]

ps can you explain your logic behind

my_array(i, 0) = ""
my_array(i, 1) = ""
my_array(i, 2) = ""



Chance,

Filmmaker, gentleman and ROMAN!

 

[JensKKK]

This is to empty the array. I create dynamic lists and they might become shorter during the update process. I noticed if I don't "" the arry I have a mixture of new entries and old entries that should not be any more in the list.

Is there a better of doing it.

The population of the array list is a two step process hence ->2 loops for next and then do loop.

 

[Chance1234]

and just to add to that

how are you setting the size of your array,

where does the value alpha and J come in,

at the moment it looks like you are adding a ton of blankspace to an array then adding your values

so 336 blank lines + yourdata

Chance,

Filmmaker, gentleman and ROMAN!

 

[JensKKK]

betweeen the two loops I have a sorting routine which allows to display data in alphabetical order or numerical order.

 

[Chance1234]

my_array(i,0)= "" just creates a blank record

If you want to clear the array in its entirety use the ERASE command

Sub Chance2()
Dim my_array As Variant

Debug.Print "populating array"

my_array = Array("a", "b", "c")

Debug.Print my_array(0) & my_array(1) & my_array(2)

Debug.Print "now we use the erase to clear the array"
Erase my_array

Debug.Print "and assign new values"

my_array = Array("d", "e", "f")
Debug.Print my_array(0) & my_array(1) & my_array(2)

End Sub

Chance,

Filmmaker, gentleman and ROMAN!

 

[JensKKK]

Nope the second loop work only from 1 to j

j is created in the for next look if dummy<>"" then j=j+1

j is max =i, but usaully smaller than i

 

[JensKKK]

OK the erase command is a usefull thing to know.

Still the listbox does not update.

i tried listbox1.update
listbox1.update=true

but both of them give me error message . So I have the wrong syntax there.

Any ideas?

 

[Chance1234]

You are still creating 336 blank lines as your

my_array(i, 0) = ""
my_array(i, 1) = ""
my_array(i, 2) = ""

falls inside the for next


I appreciate what you are trying to get at , but i think you may need to

either a) create a new array and only populate with the values you want ( take a look at Redim and preserve in the help)

or alternativly only add records to the listbox that are populated, the below needs tidying up but

x = 1
y = 1
do until x = ubound(myarray)

if myarray(x,1) <> "" then
listbox1.column(y,1) = myarray(x,1)
listbox1.column(y,2) = myarray(x,2)
y = y + 1
end if
x = x + 1

loop




Chance,

Filmmaker, gentleman and ROMAN!

 

[Chance1234]

got to shoot for the weekend, but if you add two listboxes on sheet1 , then run the following code. you will see the blank rows coming in .

Sub Chance3()
Dim my_array As Variant


my_array = Array(1, 2, 3)
Sheet1.ListBox1.List = my_array

Erase my_array

'the following is what is happenign in your code

ReDim my_array(6)

For x = 1 To 3
    my_array(x) = ""
Next

For x = 4 To 6
    my_array(x) = x
Next

Sheet1.ListBox2.List = my_array


End Sub

Chance,

Filmmaker, gentleman and ROMAN!