listbox.value is empty even though there is data!!!

[lif]

Hi,

I've been fighting with this for longer than I want to admit... I hop
there are smarter people out there than me who can explain thi
phenomenon.

Here's the situation:

I have 25 listboxes (called Pre1,Pre2,...,Pre25) which are populated i
a macro from cells in a worksheet. All listboxes have the same content
as shown in the following code:

For Each newCtl In F5DataSummary.Controls
' populate listboxes
If TypeName(newCtl) = "ListBox" And newCtl.Name Like "Pre*" Then
For i = 2 To 26
newCtl.AddItem Worksheets("Pre_Rawdata").Range("h"
i).Value
Next
End If

' NOW I WANT LISTBOX PRE1 TO HAVE ITEM 1 SELECTED BY DEFAULT, PRE2 T
HAVE ITEM 2 SELECTED,...., PRE 25 TO HAVE ITEM 25 SELECTED. I DO THI
AS FOLLOWS:


If TypeName(newCtl) = "ListBox" And newCtl.Name Like "Pre*
Then
For j = 1 To totalwaferspre
If newCtl.Name = "Pre" & j Then
Debug.Print "name="
Debug.Print newCtl.Name
ListBoxSuffix = Mid(newCtl.Name, 4, 3)
Debug.Print "suffix ="
Debug.Print ListBoxSuffix

newCtl.Selected(ListBoxSuffix - 1) = True
' newCtl.ListIndex = ListBoxSuffix - 1

Debug.Print "value="
Debug.Print newCtl.Value
Debug.Print "----"
End If
Next
End If

Next

When I look at the output of the debug.print statements th
newCtl.value shows up as empty for some instances where there actuall
is data!! If I modify the code a bit and change the way I do the loop
or other sundry things I always get a similar behavior (though no
always the same listbox shows up blank), It's as if the default valu
is not getting consistently set. If I click on the listbox manuall
then it works, but I'm trying to do this in UserForm_Initialize():

name=
Pre1
suffix =
1
value=
1
----
name=
Pre2
suffix =
2
value=
2
----
name=
Pre3
suffix =
3
value=
4
----
name=
Pre5
suffix =
5
value=
<--------- Pre5.value shows up as empty eve
though it is actually 7!!
----
name=
Pre7
suffix =
7
value=
13
----
name=
Pre6
suffix =
6
value=
8
----
name=
Pre4
suffix =
4
value=
5
----

Why is this happening??? Is there another better way to do this????
I'm using Excel 2002 / VB ver 6.3 if that helps.

Thanks so much for any help,
Lui

 

[RB Smissaert]

Maybe try this:

For i = 2 To 26
newCtl.AddItem Worksheets("Pre_Rawdata").Cells(i,8).Value
DoEvents
Next

RBS