VBA to Get columns of the list items selected in List box

Answered

Hi All,

I have total 20 columns in sheet1,

First – 8 columns are – region, Dept, leader, manager, product,subprodcut, employee name, employee type,

12 columns are 12 months (Jan – Dec)

I need the userform – where user can select any of the above first – 8 columns (single or multiple),

Once the list item selected, only those columns should paste in sheet2 along with 12 months data,

Need solution very badly 🙁

Top Contributor Asked on January 10, 2017 in VBA: User Forms.
Add Comment
2 Answer(s)
Best answer

HI,

Here is the solution for your query to get columns data from Sheet1 to Sheet2 based on list box  selection.

Here are the instructions.

  1. Go to VBA editor by clicking on Alt+F11.
  2.  Insert UserForm from the Insert Menu.
  3. Drag and drop listbox control & command button from the toolbox.
  4. Right click on the Listbox and select properties. the ‘MultiSelect’ property should be ‘1-frmMultiSelectMulti’.
  5. Select command button.
  6. Set Command button caption property to ‘Update’.
  7. Design of the userform for your reference in the below screenshot.

    VBA to get columns of the list items selected in List box

  8.  Go to Sheet1.
  9. Insert shape from the ‘Illustration’ group.
  10. Right click on shape and edit text as Show Form.
  11. VBA to get columns of the list items selected in List box

  12. Right click on the shape(Step 9) and select’Assign Macro’ from the available options.
  13. Click on New from the Assign Macro dialog box and then add following statement.
  14. Sub Rectangle1_Click()
    UserForm1.Show
    End Sub
    
  15. Now, go to VBA editor window. Double click on the userform and then add following code.
  16. Private Sub CommandButton1_Click()
    'Variable Declaration
    Dim iCnt As Integer
    Dim MyHdr() As String, count As Integer, lastRow As Integer, destCol As Integer
    count = 0: destCol = 1
    For iCnt = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(iCnt) = True Then
    ReDim Preserve MyHdr(count)
    MyHdr(count) = ListBox1.List(iCnt)
    count = count + 1
    End If
    Next iCnt
    'Find last row in Sheet1
    lastRow = Sheet1.Cells.SpecialCells(xlLastCell).Row
    For i = LBound(MyHdr) To UBound(MyHdr)
    shdr = MyHdr(i)
    For j = 1 To 8
    If Sheet1.Cells(1, j) = shdr Then
    Sheet1.Range(Sheet1.Cells(1, j), Sheet1.Cells(lastRow, j)).Copy Destination:=Sheet2.Cells(1, destCol)
    destCol = destCol + 1
    Exit For
    End If
    Next
    Next
    'Move Jan to Dec Data to Sheet2
    Sheet1.Range(Sheet1.Cells(1, 9), Sheet1.Cells(lastRow, 20)).Copy Destination:=Sheet2.Cells(1, destCol)
    'Unload userform
    Unload Me
    End Sub
    
  17. Private Sub UserForm_Initialize()
    For i = 1 To 8
    ListBox1.AddItem Sheet1.Cells(1, i)
    Next
    End Sub
    
  18. Go to sheet and then click on ‘Show Form’
  19. Now you can select multiple list items from the listbox and then click on Update button.
  20. VBA to get columns of the list items selected in List box

  21. Now you can see required output in Sheet2.

Hope it clarifies your query.

Thanks!

 

Expert Answered on January 10, 2017.

No words, just Awesome!!!!! exactly what i wanted , saved me a lot of time, thank you sooo much,

really great!!, worth joining this forum :):):)

on January 11, 2017.

I need one small extension here, apologies for that –

Can I get columns pattern/series based on the user’s selection – Ex –

1st instance – user select Dept first and then manger – here col 1 is Dept and col 2 is  manager

2nd instance – user select manger first and then  Dept ( vice -versa)  here col 1 is manager and col 2 is Dept

and goes on

.

.

.

thanks in advance,

 

on January 11, 2017.
Add Comment

Here is some useful information about the ListBox.

Thanks!

 

Expert Answered on January 10, 2017.

Once the columns pasted in sheet2, i need those columns to subtotal and show only total rows (similar to subtotal level 2 ), please help,, this will end my complete project.

 

Thanks Heaps

on January 11, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.