JasonEnsor
Programmer
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.
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.
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.