ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   ListBox1 - returns no value (https://www.excelbanter.com/excel-programming/409575-listbox1-returns-no-value.html)

steven

ListBox1 - returns no value
 
I am doing the following:

Call SortListBox(.ListBox1)
..ListBox1.ListIndex = 0
MsgBox .ListBox1.Value '*** IT RETURNS NOTHING *** WHY????
?? How do I get it to return the highlighted item (ie what I see as the 1st
item) without having to mouse click on it? I am not asking for the index
number, but what I actually see. It does not seem to recognize it as a
value until clicked but I want to return it without having to click on it.

Thank you for your help...Steven
-------------------------------------------------------
Sub SortListBox(oLb As MSForms.ListBox)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'with VBA to sort the array
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i

'Clear the listbox
oLb.Clear

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

End Sub
-------------------------------------------------------------------


Jon Peltier

ListBox1 - returns no value
 
I don't know why you're having the problems. I do know you can streamline
the code.

A slight improvement comes by replacing this:

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

with this:

oLB.List = vaItems

A more substantial improvement comes if you sort right in the listbox:

Sub SortListBox(oLb As MSForms.ListBox)
Dim i As Long, j As Long
Dim vTemp As Variant

'with VBA to sort the array
For i = 0 To oLb.ListCount - 2
For j = i + 1 To oLb.ListCount - 1
If CStr(oLb.List(i, 0)) CStr(oLb.List(j, 0)) Then
vTemp = CStr(oLb.List(i, 0))
oLb.List(i, 0) = CStr(oLb.List(j, 0))
oLb.List(j, 0) = vTemp
End If
Next j
Next i
End Sub

I did this with a form that had the listbox and two buttons, one which ran
the sorting code and selected the first item, the other that popped up the
message box. The message box always showed me the first item in the listbox.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Steven" wrote in message
...
I am doing the following:

Call SortListBox(.ListBox1)
.ListBox1.ListIndex = 0
MsgBox .ListBox1.Value '*** IT RETURNS NOTHING *** WHY????
?? How do I get it to return the highlighted item (ie what I see as the
1st
item) without having to mouse click on it? I am not asking for the index
number, but what I actually see. It does not seem to recognize it as a
value until clicked but I want to return it without having to click on it.

Thank you for your help...Steven
-------------------------------------------------------
Sub SortListBox(oLb As MSForms.ListBox)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'with VBA to sort the array
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i

'Clear the listbox
oLb.Clear

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

End Sub
-------------------------------------------------------------------




steven

ListBox1 - returns no value
 
Thank you for your response. By looking at your suggestion it made me think
harder about it and I noticed that after the sort and the set index to 0 that
the listbox did not have the focus so then I just had to set the focus back
tot he list box and now everything is ok.

Thank you,

Steven

"Jon Peltier" wrote:

I don't know why you're having the problems. I do know you can streamline
the code.

A slight improvement comes by replacing this:

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

with this:

oLB.List = vaItems

A more substantial improvement comes if you sort right in the listbox:

Sub SortListBox(oLb As MSForms.ListBox)
Dim i As Long, j As Long
Dim vTemp As Variant

'with VBA to sort the array
For i = 0 To oLb.ListCount - 2
For j = i + 1 To oLb.ListCount - 1
If CStr(oLb.List(i, 0)) CStr(oLb.List(j, 0)) Then
vTemp = CStr(oLb.List(i, 0))
oLb.List(i, 0) = CStr(oLb.List(j, 0))
oLb.List(j, 0) = vTemp
End If
Next j
Next i
End Sub

I did this with a form that had the listbox and two buttons, one which ran
the sorting code and selected the first item, the other that popped up the
message box. The message box always showed me the first item in the listbox.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Steven" wrote in message
...
I am doing the following:

Call SortListBox(.ListBox1)
.ListBox1.ListIndex = 0
MsgBox .ListBox1.Value '*** IT RETURNS NOTHING *** WHY????
?? How do I get it to return the highlighted item (ie what I see as the
1st
item) without having to mouse click on it? I am not asking for the index
number, but what I actually see. It does not seem to recognize it as a
value until clicked but I want to return it without having to click on it.

Thank you for your help...Steven
-------------------------------------------------------
Sub SortListBox(oLb As MSForms.ListBox)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'with VBA to sort the array
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i

'Clear the listbox
oLb.Clear

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

End Sub
-------------------------------------------------------------------





Jon Peltier

ListBox1 - returns no value
 
Whether the listbox has focus or not, it still has the same value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Steven" wrote in message
...
Thank you for your response. By looking at your suggestion it made me
think
harder about it and I noticed that after the sort and the set index to 0
that
the listbox did not have the focus so then I just had to set the focus
back
tot he list box and now everything is ok.

Thank you,

Steven

"Jon Peltier" wrote:

I don't know why you're having the problems. I do know you can streamline
the code.

A slight improvement comes by replacing this:

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

with this:

oLB.List = vaItems

A more substantial improvement comes if you sort right in the listbox:

Sub SortListBox(oLb As MSForms.ListBox)
Dim i As Long, j As Long
Dim vTemp As Variant

'with VBA to sort the array
For i = 0 To oLb.ListCount - 2
For j = i + 1 To oLb.ListCount - 1
If CStr(oLb.List(i, 0)) CStr(oLb.List(j, 0)) Then
vTemp = CStr(oLb.List(i, 0))
oLb.List(i, 0) = CStr(oLb.List(j, 0))
oLb.List(j, 0) = vTemp
End If
Next j
Next i
End Sub

I did this with a form that had the listbox and two buttons, one which
ran
the sorting code and selected the first item, the other that popped up
the
message box. The message box always showed me the first item in the
listbox.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Steven" wrote in message
...
I am doing the following:

Call SortListBox(.ListBox1)
.ListBox1.ListIndex = 0
MsgBox .ListBox1.Value '*** IT RETURNS NOTHING *** WHY????
?? How do I get it to return the highlighted item (ie what I see as the
1st
item) without having to mouse click on it? I am not asking for the
index
number, but what I actually see. It does not seem to recognize it as
a
value until clicked but I want to return it without having to click on
it.

Thank you for your help...Steven
-------------------------------------------------------
Sub SortListBox(oLb As MSForms.ListBox)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'with VBA to sort the array
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i

'Clear the listbox
oLb.Clear

'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i

End Sub
-------------------------------------------------------------------








All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com