Default selected item in ListBox isn't selected....

[Welshbird]

I'm trying to put into practice things I've learnt here y'know.
And at least part of this is fab (and making me very happy!).
So, I have a project which will have several forms. I on;y want top wrote the code to populate the list boxes once, as they will be the same in each case. So, I have this code in my module

Sub PopulateLists(Form)
'populate the items list and price lists on each form
'call from the form code with form name in brackets
With Form
With .lstItems
    .AddItem "0 - 430"
    .AddItem "431 - 538"
    .AddItem "539 - 646"
    .AddItem "647 - 753"
    .AddItem "754 - 861"
    .AddItem "862 - 968"
    .AddItem "969 - 1344"
    .AddItem "1345 - 1882"
    .AddItem "1883 - 2151"
    .AddItem "2152 - 2689"
    .AddItem "2690 - 3226"
    .AddItem "3227 - 3764"
    .AddItem "3765 - 4302"
    .AddItem "4303 - ...."
    .ListIndex = 6
End With
With .lstPrices
    .AddItem "0 - 2000"
    .AddItem "2001 - 4000"
    .AddItem "4001 - 6000"
    .AddItem "6001 - 8000"
    .AddItem "8001 - 10000"
    .AddItem "10001 - 12000"
    .AddItem "12001 - 14000"
    .AddItem "14001 - 16000"
    .AddItem "16001 - 18000"
    .AddItem "18001 - 20000"
    .AddItem "20001 - 22000"
    .AddItem "22001 - 24000"
    .AddItem "24001 - ...."
    .ListIndex = 6
End With
End With
End Sub

and in my form code I do this:

Private Sub userform_activate()
Call PopulateLists(SingleOptions)
End Sub

So far so good!
But, I wanted to 'read' these list boxes in the module too... so I have this in the module

Sub ReadLists(Form)
'read the selected items from both listboxes on each form
'call from the form code with the form name in brackets
Dim ItemBand As String
Dim RxBand As String
Dim I As Integer
Dim P As Integer
For I = 0 To Form.lstItems.ListCount - 1
If Form.lstItems.Selected(I) = "True" Then ItemBand = Form.lstItems.Value
Next I
For P = 0 To Form.lstPrices.ListCount - 1
If Form.lstPrices.Selected(P) = "True" Then RxBand = Form.lstPrices.Value
Next P
MsgBox ItemBand & vbCrLf & RxBand
End Sub

Called like this from the form

Private Sub cmdSingOK_click()
Call ReadLists(SingleOptions)
Unload Me
End Sub

Logically, this ought to work. But, as I have preselected an item in each list (.listindex = 6) for each box, unless I physically click on that item (even though it appears highlighted and selected the code doesn't seem to find it. It find at item which is true - but doesn't return the value to the dimmed string if I watch the code.

But it only does this for the second list box so its populating ItemBand but not RxBand.
(I tried to put them both insid a frame to look nice - then it only found the first one instead!)

I'm clearly missing something here - any ideas?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[HughLerwill]

Did you try;

For i = 0 To Form.lstItems.ListCount - 1
If i = Form.lstItems.ListIndex Then ItemBand = Form.lstItems.Text
Next i

 

[HughLerwill]

Or just;

ItemBand = Form.lstItems.Text

 

[Welshbird]

Just tried that now; and still no joy.

Is this becuase I populate the list box in the module and then read it in a different module?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Actually, striek that last question - if that were the case it wouldn't find the values for either would it....

This is driving me up the wall!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[HughLerwill]

Perhaps;

Sub ReadLists(Frm As UserForm)
'read the selected items from both listboxes on each form
Dim ItemBand As String
Dim RxBand As String

ItemBand = Frm.lstItems.Text
RxBand = Frm.lstPrices.Text
MsgBox ItemBand & vbCrLf & RxBand
End Sub

Private Sub cmdSingOK_click()
ReadLists SingleOptions
'Unload Me
End Sub

 

[Welshbird]

Still only get one of them Hugh.

I could understand if I wasn't picking up either - but just seems really odd to only be getting the selection from one list.

the list boxes are both set up the same - they are both populated in the same way. Just seems really illogical to me.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

OK - I;'ve tried populating via the RowSource in the Proerpties of the listboxes, and I get the same answer, so narrowing it down it can't be anything to do with they they are populated.

Still stuck though.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[HughLerwill]

I have just duplicated your code plus my last into a mockup here and it is working for me. Ref. Excel 2007.

 

[HughLerwill]

...and it is working for me.

Well then it started only working sometimes, especially if focus was on the lstItems ListBox when I ran the code. The following seems more reliable.

MsgBox Frm.lstItems.List(Frm.lstItems.ListIndex) & vbCrLf & Frm.lstPrices.List(Frm.lstPrices.ListIndex)

 

[Welshbird]

I actually went with this now: (In Excel 2010)

ItemBand = Form.lstItems.Value
If ItemBand = "" Then ItemBand = "969 - 1344"
RxBand = Form.lstPrices.Value
If RxBand = "" Then RxBand = "12001 - 14000"

which seems to work as these would be my default values if nothing is clicked.

Don't like it though.

Thanks all guys.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Andrzejek]

This is not really an answer to your question, but since you are eager to learn new ways to do stuff - good for you - consider this:
You do this:

Sub PopulateLists(Form)

where you pass the Form. The word Form is (probably) a reserved word which you should avoid, and it is a Variant. You may be better off by:

Sub PopulateLists(frmForm As UserForm)

Here you would pass a Form, not a Variant.

But in this case on every Form you have to have list boxes named 'lstItems' and 'lstPrices' if you want to use your Sub.

Consider passing just the list boxes to your Sub and NOT the Form they are on. Something like:

Sub PopulateLists([blue]ByRef[/blue] lstBx1 As ListBox, [blue]ByRef[/blue] lstBx2 As ListBox)

Warning - this is a VB 6 code, you should use whatever VBA has for a ListBox as a control.

The key here is [tt]ByRef[/tt]. This way your listboxes can be named whatever you want them to be, and they do not have to be named the same on the Forms.

To use it, you simply do:

Private Sub userform_activate()
Call PopulateLists(lstItems, lstPrices)
End Sub

And you do not even have to say which Form you are comming from

So your Sub would look like:

Sub PopulateLists([blue]ByRef[/blue] lstBx1 As ListBox, [blue]ByRef[/blue] lstBx2 As ListBox)[green]
'populate the items list and price lists on each form[/green]

With lstBx1
    .AddItem "0 - 430"
    .AddItem "431 - 538"
    ....
End With

End Sub

Try it - you will love it

Have fun.

---- Andy

 

[HughLerwill]

Andy - I think passing ByRef is the default in V/BA/6, so you only have to use ByVal explicitly when required.

 

[HughLerwill]

Ok I think I have a pattern. If any control on the form is moved and I run the UserForm it fails to report the Text value for the second list every other run UNLESS I SAVE the WorkBook before running the code; after that it runs reliably until the form is modified again.