Re: Looping through listbox controls

From: Vince (Vince_at_discussions.microsoft.com)
Date: 03/24/05 

Date: Thu, 24 Mar 2005 12:45:02 -0800

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 = Active***.OLEObjects(i).Name
>
> not
>
> strValue = Active***.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" <Vince@discussions.microsoft.com> wrote in message
> news:7E34FD44-D6C7-448E-BBDD-C304F143EAE9@microsoft.com...
> > Thanks Bob, I tried the following:
> >
> > Dim i as Long
> >
> > For i = 1 To Active***.OLEObjects.Count
> > If TypeName(Active***.OLEObjects(i).Object) = "ListBox" Then
> > strValue = Active***.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 Active***.OLEObjects.Count
> > > If TypeName(Active***.OLEObjects(i).Object) = "ListBox" Then
> > > Msgbox Active***.OLEObjects(i).Name
> > > End If
> > > Next i
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Vince" <Vince@discussions.microsoft.com> wrote in message
> > > news:332E2C42-9926-400B-BA7C-AF30FEDAD381@microsoft.com...
> > > > I have a work*** 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
> > > >
> > >
> > >
> > >
>
>
>