How to select specific column from the sheet and display in the excel vba list box-CodePudding

Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim lastRow As Long
idRow = sh.Range("A" & Rows.Count).End(xlUp).Row
questionRow = sh.Range("G" & Rows.Count).End(xlUp).Row
ThisWorkbook.Names.Add Name:="ID", RefersToLocal:=sh.Range("A2:B" & idRow)
ThisWorkbook.Names.Add Name:="Question", RefersToLocal:=sh.Range("G2:L" & questionRow)
With Me.listBox2
    .ColumnHeads = True
    .ColumnCount = 8
    .ColumnWidths = "30,85,85,85,85,85,85,85"
    .RowSource = "ID"
    .RowSource = "Question"
    End With
End Sub

This code only populate the list box with the data of question. How to populate the list box with both "ID" and "Question" when the form is activated? Please help!

CodePudding user response：

The second .RowSource = will overwrite the first. So either you fill the listbox manually with VBA (not using .RowSource, you will find tutorials on that) or you take all the columns between ID and Question, but you cannot have both columns if they are not continous.

Try .RowSource = "ID:Question" this will take all columns from ID to Question. Note you can hide columns by changing their .ColumnWidths to 0. For example .ColumnWidths = "30,0,40,50" will show 3 rows because the second row width is 0 and hidden.