Re: Looping through listbox controls

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 03/24/05 

Date: Thu, 24 Mar 2005 21:27:58 -0000

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" <Vince@discussions.microsoft.com> wrote in message
news:248965BA-EE89-4AE4-B8AB-2E621959A4F5@microsoft.com...
> 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >
Quantcast