VBA Listbox and loop

Eyrik Published at Dev

Eyrik

Im having a trouble understanding For and Do while.

Im trying to put items in a listbox in this order:

1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8

and so on. And the same way around

1
1 2
1 2 3

Up intil 10

How should i attack this issue?

This is what im stuck with:

Dim counter1 As Integer 
Dim counter2 As Integer 
 For counter1 = 10 To 1 Step -1 
  For counter2 = 1 To 10 
   ListBox1.Items.Add() 
  Next counter2 
 Next counter1

L42

Your problem is not with using the Loops alone but with using the ListBox Methods.
You can achieve what you want in 2 ways:

Initially concatenate (connect) the numbers into a single string and then add it on the Listbox using AddItem Method.
Populate a multicolumn ListBox using a nested loop using AddItem and List method.

The first one should be something like below:

Private Sub CommandButton1_Click()
    Dim i As Integer, j As Integer, k As Integer
    Dim s As String

    k = 11 ' this determines the exit condition
    For i = 1 To 10
        For j = 1 To 10
            If k = j Then Exit For
            s = IIf(s = "", j, s & " " & j) ' construct the string
        Next
        Me.ListBox1.AddItem s ' add in listbox
        s = ""
        k = k - 1
    Next
End Sub

Which will result to:

Edit1 No.2 Above

Private Sub CommandButton1_Click()
    Dim i As Integer, j As Integer, k As Integer
    With Me.ListBox1
        .ColumnCount = 10
        .ColumnWidths = "15;15;15;15;15;15;15;15;15;15"
        k = 10 ' determines exit condition
        For i = 1 To 10
            For j = 1 To 10
                If j = 1 Then
                    .AddItem j ' if it is the number 1, use AddItem method
                Else
                    .List(.ListCount - 1, j - 1) = j ' else use the List method
                End If
                If k = j Then Exit For
            Next
            k = k - 1
        Next
    End With
End Sub

This time, we do not add concatenated numbers into the ListBox but we add it 1 by 1 in each row and column of a multicolumn ListBox. Result would be: