Adding loop code to Userform combobox list

[Busybeaver]

Hi, I have 10 comboboxes - all require exactly the same list. Rather than having to copy the list 10 times in the coding - and changing the combobox name from listcode1, listcode2 etc, is there a loop code which I can add to do this for me??

Private Sub Userform_Initialize()




'Empty txtdate
txtdate.Value = ""

'Empty txtpayer
txtpayer.Value = ""

'Empty txtorderno
txtorderno.Value = ""



'Fill listcode1
With listcode1
.AddItem "TLNZ-1"
.AddItem "TLNZ-2"
.AddItem "TLNZ-3"
.AddItem "TLNZ-5"
.AddItem "TLNZ-6"
.AddItem "TLNZ-7"
.AddItem "TLNZ-8"
.AddItem "TLNZ-9"
.AddItem "TLNZ-10"
.AddItem "TLNZ-11"
.AddItem "TLNZ-12"
.AddItem "TLNZ-13"
.AddItem "TLNZ-14"
.AddItem "TLNZ-15"
.AddItem "TLNZ-16"
.AddItem "TLNZ-17"
.AddItem "TLNZ-18"
End With








'Set Focus on listbranch1
listbranch1.SetFocus

End Sub

Thanks

 

[sijpie]

There are two ways of doing this. But why do you have 10 identical comboboxes?

1) Just have the 'loop' in a seperate subroutine

Private Sub Userform_Initialize()




'Empty txtdate
txtdate.Value = ""

'Empty txtpayer
txtpayer.Value = ""

'Empty txtorderno
txtorderno.Value = ""

'Fill listboxes
FillListBox Listcode1
FillListBox Listcode2
FillListBox Listcode3
FillListBox Listcode4
etc

'Set Focus on listbranch1
listbranch1.SetFocus

End Sub
 
sub FillListbox (MyListbox as ListBox)
With MyListbox
.AddItem "TLNZ-1"
.AddItem "TLNZ-2"
.AddItem "TLNZ-3"
.AddItem "TLNZ-5"
.AddItem "TLNZ-6"
.AddItem "TLNZ-7"
.AddItem "TLNZ-8"
.AddItem "TLNZ-9"
.AddItem "TLNZ-10"
.AddItem "TLNZ-11"
.AddItem "TLNZ-12"
.AddItem "TLNZ-13"
.AddItem "TLNZ-14"
.AddItem "TLNZ-15"
.AddItem "TLNZ-16"
.AddItem "TLNZ-17"
.AddItem "TLNZ-18"
End With
end Sub

Option 2: use a loop

for i=1 to 18
   Listcode1.additem "TLNZ- " & i
   Listcode2.additem "TLNZ- " & i
   Listcode3.additem "TLNZ- " & i
   Listcode4.additem "TLNZ- " & i
  etc
next i

 

[wigi]

Sijpie,

In the function FillListbox, it should be:

FillListbox(MyListbox As msforms.ListBox)

 

[Busybeaver]

Awesome, thanks so much

 

[Siops]

Hi! I was trying to follow this code but I'm a bit confused. Can someone please help me where to put the Loop code? I'm trying to put a loop when choosing the list inside the combobox.

'Fill IssueComboBox
With IssueComboBox
   .AddItem "Issue1"
   .AddItem "Issue2"
   .AddItem "Issue3"
   .AddItem "Issue4"
End With

'Fill AccountComboBox
With AccountComboBox
   .AddItem "Account1"
   .AddItem "Account2"
   .AddItem "Account3"
   .AddItem "Account4"
End With

 

[sijpie]

Do you just have two combo boxes? Then there is no reason to use a loop.

The loop is outside the code to fill the comboboxes: basically you say
for each of the comboboxes do
fill

Hi! I was trying to follow this code but I'm a bit confused. Can someone please help me where to put the Loop code? I'm trying to put a loop when choosing the list inside the combobox.

'Fill IssueComboBox
With IssueComboBox
   .AddItem "Issue1"
   .AddItem "Issue2"
   .AddItem "Issue3"
   .AddItem "Issue4"
End With

'Fill AccountComboBox
With AccountComboBox
   .AddItem "Account1"
   .AddItem "Account2"
   .AddItem "Account3"
   .AddItem "Account4"
End With

 

[Norie]

siops

It does seem a bit much to use a loop for just 2 comboboxes but here goes.

arrCombos = Array("AccounComboBox", "IssueComboBox")

For I = LBound(arrCombos) To UBound(arrCombos)

     For J = 1 To 4
           Me.Controls(arrCombos(I)).AddItem "Account" & J
     Next J
Next I

 

[Siops]

Hi! THank you for your replies. What I'm actually trying to do is loop inside a ComboBox only.

When I'm choosing from the list using the "Cursor Arrow Keys" in keyboard, when I press "down arrow key" after the last item from the list, it will jump to the next ComboBox instead of going back to the first item of the list.

 

[Norie]

I don't quite see where a loop would come into that.

 

[Siops]

Hmm. Sorry for confusion. I thought I'm using the right term for that. I guess I'll just create a new thread for this. Thank you again!