I have a worksheet with 51 listbox controls on it, Listbox1 - Listbox51. I
need to loop through them and get the Value in each one and store it away. I
have looked through the questions posted here, and the answer appears easy
enough, however, I don't know if I have an Excel problem or something else.
For some reason the Me.Controls line of code will not work. When I am typing
the line of code, the drop down gives me several properties and methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 - Listbox51.
I
need to loop through them and get the Value in each one and store it away.
I
have looked through the questions posted here, and the answer appears easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am
typing
the line of code, the drop down gives me several properties and methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 - Listbox51.
I
need to loop through them and get the Value in each one and store it away.
I
have looked through the questions posted here, and the answer appears easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am
typing
the line of code, the drop down gives me several properties and methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Bob,
I figured out why the IF was never true. Sometimes you don't see the forest
for the trees! I noticed it in my reply post, I had "Listbox" rather than
"ListBox". But the second problem of picking up the Value of the listbox
still exists.

Thanks
Vince


"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 - Listbox51.
I
need to loop through them and get the Value in each one and store it away.
I
have looked through the questions posted here, and the answer appears easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am
typing
the line of code, the drop down gives me several properties and methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

for listboxes from the control toolbox toolbar

Dim i as Long
Dim strValue as String
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeOf ActiveSheet.OLEObjects(i).Object is MSForms.Listbox Then
strValue = strValue & ActiveSheet.OLEObjects(i)Object.Value &
", "
End If
Next i
msgbox strValue

--
Regards,
Tom Ogilvy



"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store it
away.
I
have looked through the questions posted here, and the answer appears
easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am

typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Vince,

My code was

strValue = ActiveSheet.OLEObjects(i).Name

not

strValue = ActiveSheet.OLEObjects(i).Value

You must learn to paste it from the post, not type it all again <vbg

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store it
away.
I
have looked through the questions posted here, and the answer appears
easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am
typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

If you want the value, you need

strValue = ActiveSheet.OLEObjects(i).Object.Value


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Vince,

My code was

strValue = ActiveSheet.OLEObjects(i).Name

not

strValue = ActiveSheet.OLEObjects(i).Value

You must learn to paste it from the post, not type it all again <vbg

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store it
away.
I
have looked through the questions posted here, and the answer
appears
easy
enough, however, I don't know if I have an Excel problem or
something
else.
For some reason the Me.Controls line of code will not work. When I
am
typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Tom,
Thank you for the help. I will ask you the same thing I asked Bob, were is
the reference information that I can look at to see when the .Object.Value is
needed etc. I have that problem a lot with Excel, I don't seem to be able to
find how to expose all the objects, properties and methods that available to
me.

Thanks again,
Vince


"Tom Ogilvy" wrote:

for listboxes from the control toolbox toolbar

Dim i as Long
Dim strValue as String
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeOf ActiveSheet.OLEObjects(i).Object is MSForms.Listbox Then
strValue = strValue & ActiveSheet.OLEObjects(i)Object.Value &
", "
End If
Next i
msgbox strValue

--
Regards,
Tom Ogilvy



"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store it
away.
I
have looked through the questions posted here, and the answer appears
easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am

typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Bob,
Thank you for the help. My original post said I needed to store the Value
of the listbox. I thought you used the .Name for example purposes, so once I
saw that it worked I then tried the .Value instead. Your code was fine, I
was just trying to get the Value, did not mean to imply that your code did
not work. Were is the reference information that I can look at to see when
the .Object.Value is needed etc. I have that problem a lot with Excel, I
don't seem to be able to find how to expose all the objects, properties and
methods that available to me.

Thanks again,
Vince


"Bob Phillips" wrote:

Vince,

My code was

strValue = ActiveSheet.OLEObjects(i).Name

not

strValue = ActiveSheet.OLEObjects(i).Value

You must learn to paste it from the post, not type it all again <vbg

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store it
away.
I
have looked through the questions posted here, and the answer appears
easy
enough, however, I don't know if I have an Excel problem or something
else.
For some reason the Me.Controls line of code will not work. When I am
typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Vince,

It was said with a smile <vbg, and I realised what you were trying
afterwards and posted some more code. This one is tricky, because there are
two objects involved here, there is an OLEObject object that acts as a
container for the controls, the ListBox in your case. When I showed the
name, that was the name of the OLEObject, which you can see in the Name Box
at the left of the Formula Bar. But most of the attributes of the control
are accessed through the OLEObjects(i).Object object. Hence the selected
value is

OLEObjects(i).Object.Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
Bob,
Thank you for the help. My original post said I needed to store the Value
of the listbox. I thought you used the .Name for example purposes, so
once I
saw that it worked I then tried the .Value instead. Your code was fine, I
was just trying to get the Value, did not mean to imply that your code did
not work. Were is the reference information that I can look at to see
when
the .Object.Value is needed etc. I have that problem a lot with Excel, I
don't seem to be able to find how to expose all the objects, properties
and
methods that available to me.

Thanks again,
Vince


"Bob Phillips" wrote:

Vince,

My code was

strValue = ActiveSheet.OLEObjects(i).Name

not

strValue = ActiveSheet.OLEObjects(i).Value

You must learn to paste it from the post, not type it all again <vbg

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox"
Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store
it
away.
I
have looked through the questions posted here, and the answer
appears
easy
enough, however, I don't know if I have an Excel problem or
something
else.
For some reason the Me.Controls line of code will not work. When
I am
typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

The object browser in the VBE.

--
Regards,
Tom Ogilvy

"Vince" wrote in message
...
Tom,
Thank you for the help. I will ask you the same thing I asked Bob, were is
the reference information that I can look at to see when the .Object.Value
is
needed etc. I have that problem a lot with Excel, I don't seem to be able
to
find how to expose all the objects, properties and methods that available
to
me.

Thanks again,
Vince


"Tom Ogilvy" wrote:

for listboxes from the control toolbox toolbar

Dim i as Long
Dim strValue as String
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeOf ActiveSheet.OLEObjects(i).Object is MSForms.Listbox
Then
strValue = strValue & ActiveSheet.OLEObjects(i)Object.Value
&
", "
End If
Next i
msgbox strValue

--
Regards,
Tom Ogilvy



"Vince" wrote in message
...
Thanks Bob, I tried the following:

Dim i as Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox" Then
strValue = ActiveSheet.OLEObjects(i).Value
End If
Next i

First problem is the If statement never goes true.
Second problem is the line strValue when looked at in the quick watch
window
gives the error: <Object doesn't support this property or method

Any idea what is going on?

Vince
"Bob Phillips" wrote:

You are probabky looking at userform answers.

Try

Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "ListBox"
Then
Msgbox ActiveSheet.OLEObjects(i).Name
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vince" wrote in message
...
I have a worksheet with 51 listbox controls on it, Listbox1 -
Listbox51.
I
need to loop through them and get the Value in each one and store
it
away.
I
have looked through the questions posted here, and the answer
appears
easy
enough, however, I don't know if I have an Excel problem or
something
else.
For some reason the Me.Controls line of code will not work. When
I am

typing
the line of code, the drop down gives me several properties and
methods to
use for the "Me." but not "Controls".
Can anyone provide some help with this!!

Thank you
Vince

Vince,

what type of "list boxes" do you have?

Created using the "Forms" (no ActiveX cotrols) toolbar or using the
Visial Basic control toolbox (pure ActiveX controls)?

I have a similar problem.
I have a sheet with 100 listboxes (but just Excel controls,NOT ActiveX
listboxes)

I want to retreive the selected values of the list boxes.
I still have no idea how to do it.
Maybe using some Java libraries I would manage to parse the sheet and
get the data,but rather I want to make it only in Excel.

I can find the listboxes using "Sheets(1).Shapes"...but that doesn't
help me to get the values :-((

I just must transform an Excel sheet to XML,but it's full of these list
boxes..
List Box 1 to List Box 9999999 :-)

THE LIST BOXES have no LINKS to CELLS (which would be nice to get the
values from the linked cells).Unfortunately I can not push the people
to send the Excel file in way,that will be easier for me.

Any ideas?