VBA Listbox and loop

Question

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

Ive been messing around with two For loops inside of eachother, but cant get it to work as all — Eyrik
– Eyrik, Commented Sep 22, 2015 at 21:11
Show what you have tried and we can help figure out the problem. — Scott Craner
– Scott Craner, Commented Sep 22, 2015 at 21:13
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 — Eyrik
– Eyrik, Commented Sep 22, 2015 at 21:27
Edit your question to add code - code in comments is difficult to read... — Tim Williams
– Tim Williams, Commented Sep 22, 2015 at 21:32

L42 · Accepted Answer · 2015-09-23 02:17:53Z

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:

I leave the ascending numbers to you. :) I hope this gets you going.

Collectives™ on Stack Overflow

VBA Listbox and loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related