tearing hair out - Listbox collection
 

I have a userform with 5 listboxes on it.

It there a neater way of cycling through each listbox control rather
than evaluating each one in turn?

Something like

for each lb in userform.listboxes
msgbox lb.name
next lb

CIA,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=378495

tearing hair out - Listbox collection
 

There should be a way to check if the control is a listbox type using
something like:
msoControl... I am not able to find right now.

Using code like:

For Each lb In UserForm1.Controls
MsgBox lb.Name
Next

lists all the controls in your userform. Using an if statement, you can
limit it to all listbox types.

A simple workaround would be to check for the names of these controls.
Suppose you have named them ListBox1, ListBox2... till 5, then you
could use:

For Each lb In UserForm1.Controls
if left(lb.name,7)="ListBox" then
MsgBox lb.Name
end if
Next


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378495

tearing hair out - Listbox collection
 

This worked for me nicely:

For Each lb In UserForm1.Controls
If LCase(Left(lb.Name, 7)) = "listbox" Then
MsgBox lb.Name
End If
Next

But the names of the listboxes need to be starting with listbox___

for example:
Listbox1
Listbox2
Listbox3
ListboxFour
etc.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378495

tearing hair out - Listbox collection
 

Dim oCtl As MSForms.Control

For Each oCtl In Me.Controls
If TypeName(oCtl) = "ListBox" Then
MsgBox oCtl.Name
End If
Next oCtl


--
HTH

Bob Phillips

"MattShoreson"
wrote in message
...

I have a userform with 5 listboxes on it.

It there a neater way of cycling through each listbox control rather
than evaluating each one in turn?

Something like

for each lb in userform.listboxes
msgbox lb.name
next lb

CIA,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=378495

tearing hair out - Listbox collection
 

cheers chaps.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=378495

tearing hair out - Listbox collection
 

Right the code to cycle thru ctls is cool. Works a treat.
However as the control is a multiselect combo...

I need to evaluate the selected items and the control object doesnt
have a listbox prop.

Any ideas on how to possibly assign the object to have listbox props or
will I just have to change the object to a variant type?


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=378495