Excel 2007 : Transfer ListBox Items to Multiple Columns

[loknath]

First of all I would like to Thank the Excel Help Forum for helping me developing my skills in Excel.
With the help of the many experts, I am learning to create an excel file for academic use in my school.
To this end I want the users to select the Class from the ComboBox and then select the corresponding sections from the ListBox. Then the selected sections are to be transferred to respective columns.
I am able to achieve this in One column only but not in other columns. Hope, the forum will help me solve this problem. I have attached the workbook and the vba codes.
Thank you all for your support.

lok Class-Section.xlsm

[royUK]

Try this code

Dim Col As Long

Private Sub ComboBox1_Change()
Dim strRange As String
    If ComboBox1.ListIndex > -1 Then
    Col = Me.ComboBox1.ListIndex + 10
       strRange = ComboBox1
       Label2.Caption = strRange
       strRange = Replace(strRange, " ", "_")
            With ListBox1
                .RowSource = vbNullString
                .RowSource = strRange
                .ListIndex = 0
            End With
    Else
       Label2.Caption = "Sections"
    End If
End Sub


Private Sub TransferButton_Click()
 Dim lItem As Long, lRows As Long, lCols As Long
 Dim bSelected As Boolean
 Dim lColLoop As Long, lTransferRow As Long
 'Pass row & column count to variables
 'Less 1 as "Count" starts at zero
 lRows = ListBox1.ListCount - 1
 lCols = ListBox1.ColumnCount - 1
 
    'Ensure they have at least 1 row selected
    For lItem = 0 To lRows
        'At least 1 row selected
        If ListBox1.Selected(lItem) = True Then
            'Boolean flag
            bSelected = True
            'Exit for loop
            Exit For
        End If
    Next
     
    'At least 1 row selected
    If bSelected = True Then
     lTransferRow = 1
        With Sheet1
        .Range(.Cells(2, Col), .Cells(7, Col)).ClearContents
        For lItem = 0 To ListBox1.ListCount - 1
                If ListBox1.Selected(lItem) = True Then
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                       'Transfer selected row to relevant row of transfer range
                       .Cells(lTransferRow, Col) = ListBox1.List(lItem, lColLoop)
                        'Uncheck selected row
                       ' ListBox1.Selected(lItem) = False
                End If
         Next
        End With
        'Unload Me
                
    Else ' NO listbox row chosen to replace yours
         MsgBox "Please Choose The Sections For The Year", vbCritical, "You must select atleat ONE Section"
    End If

End Sub

[royUK]

Thanks for the rep

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.


How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save