Excel VBA / automatically switch to second column in listbox, after said entries

[Young Grasshopper]

Hi World!

There is probably a simple solution to this, but i have a listbox that is populate by another textbox. Every time i write something in the textbox and click enter, it goes into the listbox.
But is there a simple way/code i can use, where whenever there is say 10 entries in column 1, then the next 10 goes into the second column, then the third, before 10 new ones in the first column and so on?

Or even better, that the first entry goes to column 1, second entry goes to column 2, third to column 3, fourth to second line in column 1, fifth to second line in column 2 and so on?
So it alternates between three columns (or two columns or whatever).

Would appreciate any help

 

[Legacy 143009]

Maybe something like this:

Public rowCount As Integer
Private Sub CommandButton1_Click()
  With Me
  If (.ListBox1.ListCount + 1) Mod 11 = 0 Then
    .ListBox1.ColumnCount = .ListBox1.ColumnCount + 1
    rowCount = 0
  End If
  .ListBox1.AddItem
  .ListBox1.List(rowCount, .ListBox1.ColumnCount - 1) = .TextBox1.Value
  rowCount = rowCount + 1
  End With
End Sub

 

[Legacy 143009]

One possible update could be:

Public rowCount As Integer
Private Sub CommandButton1_Click()
  With Me
  If (.ListBox1.ListCount + 1) Mod 11 = 0 Then
    .ListBox1.ColumnCount = .ListBox1.ColumnCount + 1
    rowCount = 0
  End If
  .ListBox1.AddItem
  .ListBox1.List(rowCount, .ListBox1.ColumnCount - 1) = .TextBox1.Value
  rowCount = rowCount + 1
  End With
End Sub
Private Sub UserForm_Initialize()
  rowCount = Me.ListBox1.ListCount
End Sub

 

[Young Grasshopper]

Hi Flashbond,

I can see that my question wierdly written, so I think I made it out to be much more complicated then it needed to be!
My main problem with your code was that it seems to add items to both columns when entered. But I tried your code inbetween my messy code, so it could have been some errors there.
Anyway, you gave me more then enough info so I understood how simple i could do it

This is what i ended up with:

Public rowCount As Integer

Private Sub UserForm_Initialize()
  rowCount = 0
End sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then
With Me
  
  If .ListBox1.ListCount = rowCount Then
        .ListBox1.AddItem
        End If
        
        If InfrontActive.Visible = True Then
        
        .ListBox1.List(rowCount, ColumnSelect) = .TextBox3.value & " " & .TextBox1.value
        ColumnSelect = ColumnSelect + 1
          
         ElseIf BehindActive.Visible = True Then
         
            .ListBox1.List(rowCount, ColumnSelect) = .TextBox1.value & " " & .TextBox3.value
             ColumnSelect = ColumnSelect + 1
                    
                                Else
                                
         End If
         TextBox3.value = ""
         KeyCode = 0
         
        If ColumnSelect = 2 Then
        ColumnSelect = 0
        rowCount = rowCount + 1
        End If
        End With
      
    End If
    
End Sub

Thank you for your help, and enjoy your day

 

[Legacy 143009]

Frankly, I didn't understand your code But no hard feelings if you are good with your version.

My main problem with your code was that it seems to add items to both columns when entered.

My code, first finishes 10 rows on the first column then move to the next column and so on.. Actually that was thing that what I understood from your original post.