Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Array
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... 172066cf88069 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Array
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" wrote in message oups.com... 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... 172066cf88069 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Array
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Array
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control array | Excel Programming | |||
Help with Control Array Sum Formula | Excel Discussion (Misc queries) | |||
Control Array Help... | Excel Programming | |||
Creating a Control Array | Excel Programming | |||
Dump 2D array into Control | Excel Programming |