Retrieve item names in ListBox

[Peter Jamieson]

Using Office xp, Win xp
I want to find out some details about the
controls on a worksheet, the number and
the items that head the ListBox and the
items in the list for checking purposes.

so far I have the following code:
Dim i as Integer, cntShapes as Integer
cntShapes = ActiveSheet.Shapes.Count
i = 1
Do Until i > cntShapes
shpName = ActiveSheet.Shapes(i).Name
ActiveSheet.Range("F" & i) = i
ActiveSheet.Range("G" & i) = shpName
i = i + 1
Loop

This gives me a column of numbers 1 to 36
and an adjacent column of shpName values Control 1 to
Control 36

How can I retrieve using code the items in each
Control (list box) or maybe
only the first item at the top of each list box?
Any help appreciated!

 

[Bob Phillips]

Item selected

Listbox1.Value

All items

With Activesheet.Listbox1
For i = 0 to .ListCount -1
msgbox .List(i)
Next
End With

--

HTH

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

 

[Peter Jamieson]

G'day Bob,
Thank you for your input!

When I ran the code:
With Activesheet.Listbox1
For i = 0 to .ListCount -1
msgbox .List(i)
Next
End With

I got the message:

Run-time error '438'
Object doesn't support this property or method

The error referred to: Activesheet.Listbox1

Any clues as to what the problem may be?
Cheers

 

[Bob Phillips]

Peter,

Is it a Forms control?

Try this if so

Dim i As Long

With ActiveSheet.ListBoxes("List Box 1")
For i = 1 To .ListCount
MsgBox .List(i)
Next
End With


--

HTH

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