VBA Ecel UserForm multiple selection, dymanic item list

Question

I did userForm with this instruction: http://www.excel-easy.com/vba/examples/multiple-list-box-selections.html

I tried to do dynamic item list based on table,

With ListBox_1
.RowSource = 
 Worksheets("warianty").ListObjects("in_").ListColumns(1).DataBodyRange
.ColumnHeads = True
.ColumnCount = 3
End With

it comes out blank with no list to Select from.I used object table to get dynamic range. Of course I can use other solution, but this one seemed achievable.

Then I want to copy each selected items to other table starting with first row. Here is code i tried to modify based on other button in tutorial.

 Private Sub button_save_Click()
 Dim counter As Integer
 counter = 0
 For i = 0 To ListBox_2.ListCount - 1
 If ListBox_2.Selected(i - counter) Then
 ListBox_2.copy (i - counter) 'it gives error here
Worksheets("dane wejściowe").ListObjects("Tabela41").DataBodyRange(1 + i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False,         Transpose:=True
 counter = counter + 1
 End If
 End Sub

besides improving code can SB tell me why I can't just 'copy' list ListBox.value or sth?

Nikolaos Polygenis · Accepted Answer · 2017-09-11 08:28:44Z

1

Please try the below:

Code

     Private Sub UserForm_Initialize()
  With ListBox_1

        .RowSource = Worksheets("warianty").ListObjects("in_1").ListColumns(1).DataBodyRange.Address
        .ColumnHeads = True
        .ColumnCount = 3

        End With
End Sub


        'end this:

        Private Sub button_save_Click()

        For i = 0 To ListBox_2.ListCount - 1
         If ListBox_2.Selected(i) Then

       Worksheets("dane wejsciowe").Select


        Dim new_row As ListRow
        Set new_row = Worksheets("danewejsciowe").ListObjects("Tabela41").ListRows.Add(AlwaysInsert:=True)




        new_row.Range.Cells(1, 1).Value = ListBox_2.List(i)


         End If


        Next
        End Sub

edited Sep 11, 2017 at 8:28

answered Sep 9, 2017 at 12:45

Nikolaos Polygenis

7018 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HammerUser Over a year ago

Thanks, the button works great! Unfortunately, item list is still blank, although I found another Dim lr As Long lr = Worksheets("warianty").ListObjects("in_").Range.Rows.Count With ListBox1 For i = 1 To lr .AddItem Worksheets("warianty").ListObjects("in_").DataBodyRange(i, 1).Value Next i End With

Nikolaos Polygenis Over a year ago

it works, you just add after DataBodyRange .Address.I just run it and run with no issue.Please vote my answer

HammerUser Over a year ago

Your code worked for me once when I used F8, then when I pushed F5 it went blank again. It gets proper number of row, but they are blank

Nikolaos Polygenis Over a year ago

Please use this:Private Sub UserForm_Initialize() With ListBox_1 .RowSource = Worksheets("warianty").ListObjects("in_1").ListColumns(1).DataBodyRange.Address .ColumnHeads = True .ColumnCount = 3 End With End Sub

Collectives™ on Stack Overflow

VBA Ecel UserForm multiple selection, dymanic item list

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related