highlighting in Multi List boxes

[Kamitsukenu]

Hi there,

I've got a textbox on a form that you can enter a quantity, plus a multi
list box that displays a list of tables, plus their record quantities.

I was wondering if it is possible to 'highlight' (or select) the ones equal
or greater to the quantity entered on the form.

Can anyone shed any light on this?

Thanks!

 

[Douglas J. Steele]

You'd have to write code to loop through all rows in the listbox, setting
the Selected property to True for those rows that match the criteria:

Dim intLoop As Integer

For intLoop = 0 To (Me.MyListBox.Count - 1)
Me.MyListBox.Selected(intLoop) = _
(Me.MyListBox.ItemData(intLoop) >= Me.MyTextBox)
Next intLoop

Replace MyListBox with the actual name of your listbox, and MyTextBox with
the name of the textbox that holds the quantity entered.

If MyTextBox is unbound, and the user types a value into it, put this code
in the AfterUpdate event of the text box. If it's some other scenario, post
back with the details.

 

[Kamitsukenu]

This sounds great; thanks for the feedback.

However, I have a problem where I'm having the error "Compile error, method
or data member not found":

Private Sub TxtMinimumQty_AfterUpdate()
Dim intLoop As Integer

If Len(Me.TxtMinimumQty & "") > 0 Then
Me.cmdOK.Enabled = True
lngSplitQty = Me.TxtMinimumQty
End If

For intLoop = 0 To (Me.LstSectionNames.Count - 1) 'This is where the error
is
Me.LstSectionNames.Selected(intLoop) = _
(Me.LstSectionNames.ItemData(intLoop) >= Me.TxtMinimumQty)
Next intLoop

End Sub

As I'm new to vba, I haven't got a clue why this has happened.

Any clue?

 

[Klatuu]

The correct property is ListCount
For intLoop = 0 To (Me.LstSectionNames.Count - 1)
Should be
For intLoop = 0 To (Me.LstSectionNames.ListCount - 1)

Here is an alternative that uses the ItemsSelected property.

Private Sub TxtMinimumQty_AfterUpdate()
Dim varItm As Variant

For Each varItm In Me.LstSectionNames.ItemsSelected
Me.LstSectionNames.Selected(varItem) = _
(Me.LstSectionNames.ItemData(varItem) >= Me.TxtMinimumQty)
Next varItm

End Sub

 

[Douglas J. Steele]

Oops, sorry about that, Kamitsukenu.

Thanks for the assist, Klatuu. However, I don't think your alternative will
work. I believe the intent was to select all entries that meet the criteria.
Using the ItemsSelected collection: you'd be limited to unselecting those
selected records that don't meet the criteria.

 

[Klatuu]

You are correct, Douglas.
It is early, and my caffine count is low, yet.
Rereading the post, I wonder what I was thinking.

 

[Kamitsukenu]

Hi chaps,

Thank you both for trying to aid this brain-frazzled vba newbie, but
unfortunately I am still a little lost.

I'm still having that error crop up.

Any hints perchance?

 

[Kamitsukenu]

as I understand it, my list box is populated with sql in order to give it the
rows (I have currently five rows in the listbox).

the me.mylistbox value though is null. Is this where the problem is?

Sorry if this info you shoul've had earlier...

 

[Klatuu]

First hint would be - Send the code, the error you are getting, and what line
the error is occuring on.

 

[Kamitsukenu]

It's the one I mentioned earlier:

"Compile error, method or data member not found":

as I understand it, my list box is populated with sql in order to give it the
rows (I have currently five rows in the listbox).

the me.mylistbox value though is null. Is this where the problem is?

Sorry if this info you shoul've had earlier...

 

[Kamitsukenu]

Dim intloop As Integer
For intloop = 0 To (Me.LstSectionNames.Count - 1)
Me.LstSectionNames.Selected(intloop) = _
(Me.LstSectionNames.ItemData(intloop) >= Me.TxtMinimumQty)

Next intloop

The error occurs at the count stage.

 

[Klatuu]

I think you missed my earlier post. The property you want is ListCount.
There is no Count property.
Dim intloop As Integer
For intloop = 0 To (Me.LstSectionNames.ListCount - 1)
Me.LstSectionNames.Selected(intloop) = _
(Me.LstSectionNames.ItemData(intloop) >= Me.TxtMinimumQty)

Next intloop

Also, it is normal for Me.LstSectionNames to return Null. A Multi Select
Listbox has no value on it's own. You have to address each row as
Me.LstSectionNames.ItemData(n) Where n is 0 to .ListCount -1

 

[Kamitsukenu]

Doh!

Sorry, I misread your post; major apologies - and thank you. I'll give it a
crack when I get back to work tomorrow

Thank you again.

 

[Kamitsukenu]

hello again,

Okay,

The problem i'm gettimg now is that everything in the listbox is been
selected.

This line:
(Me.LstSectionNames.ItemData(intloop) >= Me.TxtMinimumQty)

doesn't seem to be picking up the quantities of records in the tables, so
the txtminimum isn't been used correctly.

Any ideas?


Next intloop

 

[Douglas J. Steele]

Let's see the actual code you're trying to use.

 

[Kamitsukenu]

Dim intloop As Integer
For intloop = 0 To (Me.LstSectionNames.ListCount - 1)
Me.LstSectionNames.Selected(intloop) = _
(Me.LstSectionNames.ItemData(intloop) >= Me.TxtMinimumQty)
Next intloop

there's some SQL before that. Do you need that as well?

 

[Douglas J. Steele]

What are typical values? If they're numeric, you might want to use

Me.LstSectionNames.Selected(intloop) = _
(CLng(Me.LstSectionNames.ItemData(intloop)) >=
CLng(Me.TxtMinimumQty))

 

[Kamitsukenu]

Okay, sorry, next problem, I've just realised that column 1 of the listbox
indicates the section name, and column 2 is the quantity that needs to be
compared to me.txtquantity. How do I extract the quantity column?

 

[Douglas J. Steele]

Me.LstSectionNames.Column(1, intloop)

will give you the value from the 2nd column (the Column collection starts
numbering at 0)

Me.LstSectionNames.Column(0, intloop) should give you the same result as
Me.LstSectionNames.ItemData(intloop) (assuming that the 1st column is the
bound column)

 

[Kamitsukenu]

Douglas, Klaatu,

Thank you so much for all your help and ;patience in this; I finally got
this thing working!

Best Wishes to you both,

Kamitsukenu