Hi All,

I am not very good in programing and hence this query.
I wanted to use a control array of ListBoxes. I had made that in VB,
I could not do that in VBA.
When I searched here, I found the following topic and understood that
it is not possible.

http://groups.google.co.in/group/mic...8172066cf88069

But I could not understand the thread fully so as to use it for my
use. Can someone make it more simpler..

I have a number of controls (Listboxes) with names, Level01, Level02,
Leve03....
How can I refer to each listboxes with an index.

add_to_level(01,"text1") would be equivalent to Level01.additem
"text1"

Now I am using "Select Case" option to manually refer to each list
box. But if I can include everything in a single For Loop, that would
be of grat help...

Thanks
Joe VJ

Hi Joe
You could use an intermediate function to get to your control's properties,
this could be done easily since your controls seem to be named logically.
eg:
Function GetCtrlObj(Usf As UserForm, strCtrl As String) As Object
On Error GoTo GetCtrlObj_Error

Set GetCtrlObj = Usf.Controls(strCtrl)

On Error GoTo 0
Exit Function

GetCtrlObj_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
GetCtrlObj of Feuille UserForm1"
End Function

Then using this function, get to the control's Additem method:
For i = 1 To 3
addtext = GetCtrlObj(UserForm1, "Level0" & i).AddItem("text" & i)
Next i

HTH
Cordially
Pascal

"Joe" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> Hi All,
>
> I am not very good in programing and hence this query.
> I wanted to use a control array of ListBoxes. I had made that in VB,
> I could not do that in VBA.
> When I searched here, I found the following topic and understood that
> it is not possible.
>
> http://groups.google.co.in/group/mic...8172066cf88069
>
> But I could not understand the thread fully so as to use it for my
> use. Can someone make it more simpler..
>
> I have a number of controls (Listboxes) with names, Level01, Level02,
> Leve03....
> How can I refer to each listboxes with an index.
>
> add_to_level(01,"text1") would be equivalent to Level01.additem
> "text1"
>
> Now I am using "Select Case" option to manually refer to each list
> box. But if I can include everything in a single For Loop, that would
> be of grat help...
>
> Thanks
> Joe VJ
>

Hi Joe,

As stated in the other thread "Control Arrays" do not exist in VBA as they
do in VB.

From what you described Pascal's suggestion should more than fulfil your
requirements. However you may also find some things in the following demo
that in effect give some similar functionality to a control array.

A userform with 3 listbox's and a class module named clsLB

' Userform with 3 listboxes named
' ListBox1, ListBox2, ListBox3

Private arrClsLB() As clsLB

Private Sub UserForm_Initialize()
Dim i As Long, j As Long
ReDim arrClsLB(0 To 2)

For i = 0 To 2
Set arrClsLB(i) = New clsLB
Set arrClsLB(i).mLB = Me.Controls("ListBox" & i + 1)
arrClsLB(i).idx = i
Next

For i = 0 To 2
For j = 1 To 2
arrClsLB(i).mLB.AddItem arrClsLB(i).mLB.Name & _
" Line " & j
Next
Next

End Sub

' class named clsLB

Public WithEvents mLB As MSForms.ListBox
Public idx As Long

' in the middle combo above select mLB
' select other events in the right dropdown above

Private Sub mLB_Click()
MsgBox "mLB.ListIndex " & mLB.ListIndex & vbCr & _
"mLB.Name " & mLB.Name & vbCr & vbCr & _
mLB.List(mLB.ListIndex)

End Sub

Regards,
Peter T

PS, for your listboxes named Level01 et
use "Level" & right$("0" & i, 2) ' or maybe ("0" & i - 1, 2)


"Joe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
>
> I am not very good in programing and hence this query.
> I wanted to use a control array of ListBoxes. I had made that in VB,
> I could not do that in VBA.
> When I searched here, I found the following topic and understood that
> it is not possible.
>
>
http://groups.google.co.in/group/mic...8172066cf88069
>
> But I could not understand the thread fully so as to use it for my
> use. Can someone make it more simpler..
>
> I have a number of controls (Listboxes) with names, Level01, Level02,
> Leve03....
> How can I refer to each listboxes with an index.
>
> add_to_level(01,"text1") would be equivalent to Level01.additem
> "text1"
>
> Now I am using "Select Case" option to manually refer to each list
> box. But if I can include everything in a single For Loop, that would
> be of grat help...
>
> Thanks
> Joe VJ
>

> I have a number of controls (Listboxes) with names, Level01, Level02,
> Leve03....
> How can I refer to each listboxes with an index.
>
> add_to_level(01,"text1") would be equivalent to Level01.additem
> "text1"

You can use this subroutine to add text to your ListBoxes...

Sub Add_To_Level(LevelNumber As String, Text As String)
Me.Controls("Level" & LevelNumber).AddItem Text
End Sub

Just use the syntax you suggested in your posting. For example...

Add_To_Level "01", "One"
Add_To_Level "02", "Two"
Add_To_Level "03", "Three"

will add 'One' to the ListBox named Level01, 'Two' to the ListBox named
Level02 and 'Three' to the ListBox named Level03. If you wanted to work this
in a For-Next loop, maybe something like this...

Items = Array("One", "Two", "Three")
For X = 1 to 3
Me.Controls("Level" & Format(X, "00")).AddItem Items(X-1)
Next

which will do the same thing as the 3 lines of code at the beginning do.

Rick

small typo -

> PS, for your listboxes named Level01 et
> use "Level" & right$("0" & i, 2) ' or maybe ("0" & i - 1, 2)

' or maybe ("0" & i + 1, 2)

if say maintaining a zero base index method with first name starting at '01
as in the demo

Peter T

On Oct 16, 2:40 pm, "Peter T" <peter_t@discussions> wrote:
> small typo -
>
> > PS, for your listboxes named Level01 et
> > use "Level" & right$("0" & i, 2) ' or maybe ("0" & i - 1, 2)
>
> ' or maybe ("0" & i + 1, 2)
>
> if say maintaining a zero baseindexmethod with first name starting at '01
> as in the demo
>
> Peter T

Thanks everyone.... These are very useful for me..
I especially liked these as I think these are easy for me to
understand...

GetCtrlObj(UserForm1, "Level0" & i).additem("Text")
Me.Controls("Level" & Format(X, "00")).AddItem Items(X-1)


Thanks again.. I will comeback once I try this....

Regards
Joe