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!

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" wrote in message
...
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!

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" wrote in message
...
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" wrote in message
...
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!

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)

"Peter Jamieson" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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!