Looping through listbox controls
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
|