ListBox control not being recognized by Macro

[battists]

Hello all,

I'm a novice at VBA, struggling with an unhelpful listbox. I'm tryin
to ultimately write code that populates an array with values picke
from a multi-select list box.

While testing, here's what I've done:

I created a brand new spreadsheet, created a list box using the Contro
Toolbox called lbServers.

I then created a macro that only does the following:


Code
-------------------

Sub Test()
Dim Servers(10) As String
Set MyListBox = Sheet1.lbServers
y = 1
For x = 1 To MyListBox.Items.Count '<---problem spot
If MyListBox.Selected(x) Then
Servers(y) = MyListBox.Columns(0, x)
y = y + 1
End If
Next x
End Sub

-------------------


When I step through this code, it sets MyListBox with a Type o
Variant/Object/ListBox. This object does not have an Items propert
(collection?) when I look at it in the Locals window. If I kee
stepping, I reach the problem spot, and it returns "Object does no
support this property or method."

It seems that for some reason it's not recognizing the object as
ListBox. AutoComplete finds the control in Sheet1, but if I typ
"Sheet1.lbServers." it can't find any properties at all.

Any suggestions? This is on Excel 2003.

Thanks!

Stev

 

[Harald Staff]

Hi

Three things here:
Declare the box as MSForms.Listbox. There are other listboxes that may
confuse the old lady.

Items.Count is no property of those. The number you want is ListCount. And

Unless you state otherwise, VBA is zero based. So item 1 is List(0), and the
last item is List(ListCount-1).

This should work:

Sub test()
Dim X As Long
Dim MyListbox As MSForms.ListBox
Set MyListbox = Sheet1.LBServers
For X = 0 To MyListbox.ListCount - 1
If MyListbox.Selected(X) Then
'things happen
End If
Next
End Sub

Pretty good code for a novice though. Good work ;-)

HTH. Best wishes Harald