Setting ListIndex = 0 does not change Listbox.Value on activate

[Dianne]

Using Excel 97.

I have a user form with 2 single-select list boxes. The first (lstTypes)
shows a list of available Types. The second (lstDescriptions) shows the
Descriptions for the selected Type. I have a routine (ExtractCodes)
which sets values in a criteria range (lstTypes does not have a control
source) and extracts the matching data from the database.

I call the ExtractCodes routine in lstTypes_Change event. This works
well.

BUT... When I activate the userform for the first time, I would like the
first
item in lstTypes to be selected. So the UserForm Activate event sets the
ListIndex to 0 (I can see the first item is "selected" -- highlighted in
blue). This triggers the lstTypes_Change event, which runs ExtractCodes.

However, when I step through ExtractCodes, lstTypes.value = "" so the
criteria range is set to "", which means that lstDescriptions shows all
values.

Why doesn't ListIndex=0 change lstTypes.Value even though it's
triggering the Change event?

Thanks for any suggestions.

 

[Bob Phillips]

Dianne,

I just did a little test with this code

Private Sub ListBox1_Click()
With ListBox2
Select Case ListBox1.Value
Case "Bob"
.AddItem 1
.AddItem 2
Case "Lynne"
.AddItem 11
.AddItem 12
End Select
End With
End Sub

Private Sub Userform_Activate()
With ListBox1
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.ListIndex = 0
End With
End Sub

and when it entered the Listbox1_Click event from the form activate, the
value was Bob as expected. so it works as you would expect.

Show your code, and explain the data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dianne]

Thanks for your response, Bob.

I've sort of figured it out -- I was using the wrong property (between
Access, VB and Excel
comboboxes and listboxes multiselect and single, I lose track
sometimes!).

What I had in my code to set the criteria values for my AdvancedFilter
was:

With frmSelectSAP
If IsNull(.lstTypes.Value) Then
strType = ""
Else
strType = .lstTypes.Value
End If
End With

When I changed

strType = .lstTypes.Value
to
strType = .lstTypes.List(frmSelectSAP.lstTypes.ListIndex)

it worked fine.

The bizarre thing was that this was only a problem on activation, never
subsequently, even though the same procedure is used. Does clicking on
the value in the list change the listbox's Value but setting the
ListIndex by code doesn't?

The other bizarre thing is that the problem with .lstTypes.Value doesn't
happen every time. One time I open my workbook, run the form and
everything works fine. Then I close my workbook and open it again, and
then I get the same problem.

At any rate, I've changed the code to strType =
..lstTypes.List(frmSelectSAP.lstTypes.ListIndex) and it's working fine.

Thanks again, and sorry to mess you about.