Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox.value is empty even though there is data!!!
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 -- li ----------------------------------------------------------------------- lif's Profile: http://www.excelforum.com/member.php...fo&userid=3574 View this thread: http://www.excelforum.com/showthread.php?threadid=55752 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox.value is empty even though there is data!!!
Maybe try this:
For i = 2 To 26 newCtl.AddItem Worksheets("Pre_Rawdata").Cells(i,8).Value DoEvents Next RBS "lif" wrote in message ... Hi, I've been fighting with this for longer than I want to admit... I hope there are smarter people out there than me who can explain this phenomenon. Here's the situation: I have 25 listboxes (called Pre1,Pre2,...,Pre25) which are populated in a macro from cells in a worksheet. All listboxes have the same contents 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 TO HAVE ITEM 2 SELECTED,...., PRE 25 TO HAVE ITEM 25 SELECTED. I DO THIS 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 the newCtl.value shows up as empty for some instances where there actually is data!! If I modify the code a bit and change the way I do the loops or other sundry things I always get a similar behavior (though not always the same listbox shows up blank), It's as if the default value is not getting consistently set. If I click on the listbox manually 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 even 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, Luis -- lif ------------------------------------------------------------------------ lif's Profile: http://www.excelforum.com/member.php...o&userid=35745 View this thread: http://www.excelforum.com/showthread...hreadid=557522 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
Empty listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |