Add multiple lines from ListBox in columns

[pasjauo]

Hey guys,

I have a problem I can't really seem to get working.

As of now I have a userform where you can input in to textboxes, listbox and adds it to another sheet. That works fine.

Right now i want to give the user "add item" listboxes where they can add multiple items in the left listbox (listbox1) to the right listbox(listbox2). That works fine. What is not working is the transfer from listbox2 to columns. The problem is that people can add 1 or 7 items, so it has to take in to account how many items are added to listbox2. Furthermore it needs to start adding it to column 7.

So 1st line in listbox2 should be at column 7, 2nd line in column 8 and so on. Hope you understand.

Right now i'm using emptyRow to insert the inputs in to the columns right now.


You can see my "add" button here:

Private Sub tilfoejsag_knap_Click()

Dim emptyRow As Long


'Make Sheet1 active
Worksheets("Automater1").Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 1).Value = box1.Value
Cells(emptyRow, 2).Value = box2.Value
Cells(emptyRow, 3).Value = box3.Value
Cells(emptyRow, 4).Value = box4.Value
Cells(emptyRow, 5).Value = box5.Value
Cells(emptyRow, 6).Value = box6.Value




Unload Me


Worksheets("Startside").Activate


MsgBox "Sag tilføjet"


End Sub

What can i do to add the lists?

Thanks in advance guys!

 

[Domenic]

To transfer items from ListBox2 to your worksheet, starting at Column G, try...

    Dim i As Long    
    With Me.ListBox2
        If .ListCount = 0 Then
            MsgBox "No items listed in ListBox2!", vbExclamation
            Exit Sub
        End If
        For i = 0 To .ListCount - 1
            Worksheets("Sheet1").Range("G1").Offset(, i).Value = .List(i)
        Next i
    End With

Change the sheet name accordingly.

Hope this helps!

 

[pasjauo]

Thanks Domenic,

I tried to implement your solution into my working code, but it does not seem to do anything.

This is my full code for the command button:

Private Sub tilfoejsag_knap_Click()
 
Dim emptyRow As Long
Dim i As Long
 
'Make Sheet1 active
Worksheets("Automater1").Activate
 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
 
'Transfer information
Cells(emptyRow, 1).Value = box1_box.Value
Cells(emptyRow, 2).Value = box2_box.Value
Cells(emptyRow, 3).Value = box3_box.Value
Cells(emptyRow, 4).Value = box4_box.Value
Cells(emptyRow, 5).Value = list1_list.Value
Cells(emptyRow, 6).Value = box5_box.Value
 
If OptionButton1.Value = True Then
    Cells(emptyRow, 8).Value = "text text 1"
End If
If OptionButton2.Value = True Then
    Cells(emptyRow, 8).Value = "text text 2"
End If
If OptionButton3.Value = True Then
    Cells(emptyRow, 8).Value = "text text 3"
End If
If OptionButton4.Value = True Then
    Cells(emptyRow, 8).Value = "text text 4"
End If
If OptionButton5.Value = True Then
    Cells(emptyRow, 8).Value = "text text 5"
   
    With Me.add_box
        If .ListCount = 0 Then
            MsgBox "No items listed in ListBox2!", vbExclamation
            Exit Sub
        End If
        For i = 0 To .ListCount - 1
            Worksheets("Sheet1").Range("G:G").Offset(, i).Value = .List(i)
        Next i
    End With
   
End If
 
Unload Me
 
Worksheets("Startside").Activate
 
MsgBox "Sag tilføjet"
 
End Sub

My Listbox2 is called add_box and my sheet name is Sheet 1. I must be doing something wrong?

 

[Domenic]

First, I'm not sure why in the following line of code...

Worksheets("Sheet1").Range("G:G").Offset(, i).Value = .List(i)

...you refer to the entire column, instead of a single cell. If, for example, you want the items listed in the first row, starting at Column G, it should be...

Worksheets("Sheet1").Range("G1").Offset(, i).Value = .List(i)

Secondly, at the beginning of your code, you make Sheet1 active, but refer to Worksheets("Automater1"). So it looks like Sheet1 refers to the code name, not the sheet name. Therefore, to refer to the sheet name, use the following instead...

Worksheets("Automater1").Range("G1").Offset(, i).Value = .List(i)

Or, to refer to the code name...

Sheet1.Range("G1").Offset(, i).Value = .List(i)

 

[pasjauo]

First, I'm not sure why in the following line of code...

Worksheets("Sheet1").Range("G:G").Offset(, i).Value = .List(i)

...you refer to the entire column, instead of a single cell. If, for example, you want the items listed in the first row, starting at Column G, it should be...

Worksheets("Sheet1").Range("G1").Offset(, i).Value = .List(i)

Secondly, at the beginning of your code, you make Sheet1 active, but refer to Worksheets("Automater1"). So it looks like Sheet1 refers to the code name, not the sheet name. Therefore, to refer to the sheet name, use the following instead...

Worksheets("Automater1").Range("G1").Offset(, i).Value = .List(i)

Or, to refer to the code name...

Sheet1.Range("G1").Offset(, i).Value = .List(i)

Thanks again for the reply Domenic,

I was actually just mingling around with the code to try and make it work. The rest of the is right now searching for next empty row in the designated sheet, so I don't think referring to a single cell with do any good, or what? I am by no means an expert, that is why I am trying myself to learn and to figure it out by trying new things myself.

I want the things listed starting from column G, where the rest of the code is placing the row. I dont know if its row 1 or 25, depends on how many entries there is.

And the sheet name has been correct, it was just bad copy pasting from my part, my apologies I have been using "Automater1" any place necessary.

Hopefully you understand what i want to do with the userform box. If you can think of another simple way of using the Userform box with multiple entries on a list, please let me know.

 

[Domenic]

I want the things listed starting from column G, where the rest of the code is placing the row.

That's what my initial thought was, but you say that you want to start at Column G, while you already have something being written to Column H. So I'm a bit confused. In any case, if you want to start at the row defined by emptyRow, starting at Column G, try...

Worksheets("Automater1").Range("G" & emptyRow).Offset(, i).Value = .List(i)

Does this help?