Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA add multiple columns of data to listbox 2

Status
Not open for further replies.

JasonEnsor

Programmer
Sep 14, 2010
193
GB
Hi Guys,

I am looking for a little help on showing multiple columns of data in a listbox. If i was showing a complete range of data say A1:B3 i know i can do that quite simply. What i am struggling with is how to add rows to my listbox based on criteria.

I have a single workbook, within it are a tab for each day of the week and a tab for administration purposes (not the best designed workbook but i am working with the tools i have been given). On each of the day tabs are student details such as student number, module code, and module start and end time.

My hope is that i can use a userform to search for a student number then populate a multipage control with a listbox for each day based on the information found on the workbook.

Example Data:
On the Monday Worksheet i have:

Student Number Module Code Start Time End Time
1 ABC123 09:00 10:00
2 ABC123 09:00 10:00
1 ABC125 11:00 12:00

When searching for the Student Number 1, i would expect my userform to have on the lstMonday listbox the following
1 ABC123 09:00 10:00
1 ABC125 11:00 12:00

I have posted the code i have so far which works, apart from it only adds one column of data to the listbox. i am guessing it is going to be something simple that i am missing as usual.

ideally after i have that section sorted i would like to be able to double click on a listbox entry and for the information to populate another userform for me to amend the data before saving back to the worksheet.

Code:
Private Sub cmdSearch_Click()

    Dim ws_Current As Worksheet
    Dim StudentNumber As String
    Dim TempStudentNumber As String
    Dim currentLastRow As Long
    Dim currentRow As Long
    Dim dayValue As String
    
    StudentNumber = frmDiary.txtStudentNumber.Value
    
    For Each ws_Current In Worksheets
        ' if it is not the admin sheet then we want to check it out
        If ws_Current.Name <> "Administration" Then
            
            ws_Current.Activate
            currentLastRow = lastRow(ws_Current)
            
            If currentLastRow > 0 Then
                For Each student In ActiveSheet.Range("A1:A" & currentLastRow)
                    student.Rows.EntireRow.Select
                    currentRow = ActiveCell.Row
                
                    TempStudentNumber = ActiveSheet.Range("A" & currentRow)
                
                    If (StrComp(TempStudentNumber, StudentNumber, vbTextCompare) = 0) Then
                    
                        studentFound = True
                
                        If studentFound Then
                        
                            dayValue = ws_Current.Name
                        
                            Select Case (dayValue)
                                Case "Monday"
                                    frmDiary.lstMonday.AddItem (ActiveSheet.Range("B" & currentRow))
                                Case "Tuesday"
                                    frmDiary.lstTuesday.AddItem (ActiveSheet.Range("B" & currentRow))
                            End Select
                        End If
                    End If
                
                Next student
            
            End If

        End If
    Next

End Sub

The code is still pretty rough around the edges and could possibly do with some tweaking, but any help or advice would be appreciated.

Regards

J.
 
hi,

Check out Excel VBA Help on ColumnCount

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
If your ListBox has 3 columns, I'd try something like this:
frmDiary.lstMonday.AddItem ws.Range("B" & currentRow & ":D" & currentRow)

Another way is to play with the frmDiary.lstMonday.List property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I might go about this a different way.

I'd use the StudentNumber, however you arrive at that value, as a criteria for a series of queries like this on a sheet called query. This assigns the query results to the listbox
Code:
Sub click()
    Dim ws As Worksheet, sSQL As String
    Const UNI = " union  all "
    
    sSQL = ""
    
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Administration", "query"
            Case Else
                If Len(sSQL) > 0 Then sSQL = sSQL & UNI
                sSQL = sSQL & "select [Student Number], [Module Code], [Start Time], [End Time]"
                sSQL = sSQL & vbLf
                sSQL = sSQL & "from [" & ws.Name & "$]"
                sSQL = sSQL & vbLf
                sSQL = sSQL & "where [Student Number]=" & frmDiary.txtStudentNumber.Value & ""
        End Select
    Next
    
    With wsQuery.ListObjects(1)
        With .QueryTable
            .CommandText = sSQL
            .Refresh False
        End With
        frmDiary.txtStudentNumber.ListFillRange = .Parent.Name & "!" & .DataBodyRange.Address
    End With
    
End Sub


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Hi Guys,

Thanks for the advice. I have never used SQL queries in VBA so that is something i am going to look at further as i think it will be a useful tool to add to my VBA toolbox. Due to this being a prototype to see if it suits the users needs i have used the following:

Code:
 Select Case (dayValue)
                                Case "Monday"
                                         
                                    With frmDiary.lstMonday
                                        .ColumnCount = colCount
                                        .ColumnWidths = colWidths
                                        .AddItem
                                        .List(MonCount, 0) = StudentsNumber ' Student Number
                                        .List(MonCount, 1) = ModuleCode ' Module Code
                                        .List(MonCount, 2) = ModuleTitle ' Module Title
                                        .List(MonCount, 3) = StartTime ' Start time
                                        .List(MonCount, 4) = EndTime ' End time
                                        .List(MonCount, 5) = Site ' Site
                                        .List(MonCount, 6) = Room ' Room
                                        .List(MonCount, 7) = SupportWorker ' Support Worker
                                        .List(MonCount, 8) = InterpElecNote ' Interpeter/Electronic
                                        .List(MonCount, 9) = ClaimAt & vbTab & TypeOfSupport
                            
                                    End With
                                    MonCount = MonCount + 1

I will definatly be updating it to use SQL though once the user has agreed that it meets the required functionality

Many Thanks again

J.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top