Mystery: The ComboBox Text Property (XL2004 for Mac)

[Baritone]

According to the Excel 2004 for Mac Help text:

"For a ComboBox, you can use Text to update the value of the control.
If the value of Text matches an existing list entry, the value of the
ListIndex property (the index of the current row) is set to the row
that matches Text. If the value of Text does not match a row, ListIndex
is set to -1."

Try as I might, I cannot get this to work as advertised.

Using VBE, I am trying to fill UserForm controls using data from a
spreadsheet. When I open the UserForm, I would like to have a ComboBox
set to the list item represented by an entry on my spreadsheet. The
listindex property will be used to trigger other changes on the
spreadsheet.

The structure is exactly like the very simple (but clever) sheet I
found online at this url:

http://www.xl-logic.com/xl_files/vba/userform_controls.zip

For my test I added a ComboBox to this sheet, and populated it
(ComboBox1.List = Array("Red", "Blue", "Green") and assigned a cell
reference.

The ComboBox accepts the text (i.e., Red), but the ListIndex property
remains at -1. And if I change the ComboBox style property to
fmStyleDropDownList, I get a runtime error 380 "Could not set the Text
Property. Invalid property value".

If the Help text is correct, either the text entries are not identical
in some way, or I am missing something simple.

I have tried everything I can think of and searched seached the web for
hours to get ideas.

The slightest hint or idea would be appreciated.

Bari

 

[Bob Greenblatt]

According to the Excel 2004 for Mac Help text:

"For a ComboBox, you can use Text to update the value of the control.
If the value of Text matches an existing list entry, the value of the
ListIndex property (the index of the current row) is set to the row
that matches Text. If the value of Text does not match a row, ListIndex
is set to -1."

Try as I might, I cannot get this to work as advertised.

Using VBE, I am trying to fill UserForm controls using data from a
spreadsheet. When I open the UserForm, I would like to have a ComboBox
set to the list item represented by an entry on my spreadsheet. The
listindex property will be used to trigger other changes on the
spreadsheet.

The structure is exactly like the very simple (but clever) sheet I
found online at this url:

http://www.xl-logic.com/xl_files/vba/userform_controls.zip

For my test I added a ComboBox to this sheet, and populated it
(ComboBox1.List = Array("Red", "Blue", "Green") and assigned a cell
reference.

The ComboBox accepts the text (i.e., Red), but the ListIndex property
remains at -1. And if I change the ComboBox style property to
fmStyleDropDownList, I get a runtime error 380 "Could not set the Text
Property. Invalid property value".

If the Help text is correct, either the text entries are not identical
in some way, or I am missing something simple.

I have tried everything I can think of and searched seached the web for
hours to get ideas.

The slightest hint or idea would be appreciated.

Bari

This works fine for me. Perhaps it was a change in SP 1. Have you upgraded?
I can not test a prior version. Here is the code I used to test:

Private Sub UserForm_Activate()
With ComboBox1
.Clear
For Each xx In Sheets(1).Range("a1:a4")
.AddItem xx
Next
.Text = Sheets(1).Range("a3")
MsgBox .ListIndex
End With
End Sub

I simply created a user form with one combo box. When running the form, it
shows the correct list, and selected item. The list index also seems to
change correctly.

 

[Baritone]

This works fine for me. Perhaps it was a change in SP 1. Have you upgraded?
I can not test a prior version. Here is the code I used to test:

Private Sub UserForm_Activate()
With ComboBox1
.Clear
For Each xx In Sheets(1).Range("a1:a4")
.AddItem xx
Next
.Text = Sheets(1).Range("a3")
MsgBox .ListIndex
End With
End Sub

I simply created a user form with one combo box. When running the form, it
shows the correct list, and selected item. The list index also seems to
change correctly.

Thanks Bob!

Your approach worked perfectly.

For my education, though, I would be appreciate any thoughts on why
filling the ComboBox with an array sent to the .List property, followed
by a cell reference sent to the .Text property failed to produce the
same result.

Thanks again.

B