[Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
Hello Community,
i'm new here since today and i'm from germany. It's hard for me to explain my Problem because my english is not so well and the goolge translator a piece of garbage.
Okay now i start with my main Problem:
I wann click in ListBox1, okay done thats not so hard for me. then in this ListBox1 are 5 Columns.
I wanna split those 5 columns in 5 own values and search for in the Worksheet1. okay now i get my data and wanna post this in ListBox2.
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
to match that many criteria, i would use ADO with an sql query to return a recordset of matching results, then fill the listbox2 from the recordset
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
okay that was my first idea too, but i can't use that in the office because the IT-Man is a piece of cake and i'm "only" a trainee with an unimportant exercise ... i can upload the File if you want
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
i can upload the File if you want
do that, i will have a look, zip the workbook first
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
would any of the records in the kurse sheet (listbox3) actually return matching results from detenquelle worksheet, i ignore the last criteria (max participants) as it does not exist in the searched data, as far as i could see
i struggle with the language differences, but i believe i have a working solution, just no matching data is returned, so more testing is required
please confirm the translations of the fields to match in the worksheet, as i may have got some wrong
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
I just need the Columns 1-3 in the ListBox. The Max participants is just to see how many people are in this course allowed.
I apologize for any inconvenience.
Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2
Hell Yeah i found a solution
Code:
Dim lZeile As Long
Dim LetzteZeile As Integer
Dim objWks As Worksheet
Dim i As Integer
Dim p As Integer
ListBox4.Clear
lZeile = 7
Set objWks = ThisWorkbook.Worksheets("Datenquelle")
LetzteZeile = objWks.Range("C7").SpecialCells(xlCellTypeLastCell).Row
p = 1
With ListBox3
.ColumnCount = 5
.ColumnHeads = False
For i = 0 To .ListCount - 1
If .Selected(i) Then
For lZeile = 7 To LetzteZeile
If .List(i, 0) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 12).Value)) _
And .List(i, 1) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 11).Value)) _
Then
With ListBox4
.ColumnCount = 6
.ColumnHeads = False
.AddItem p
.List(.ListCount - 1, 1) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 3).Value))
.List(.ListCount - 1, 2) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 2).Value))
.List(.ListCount - 1, 3) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 5).Value))
.List(.ListCount - 1, 4) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 9).Value))
.List(.ListCount - 1, 5) = VBA.Trim(CStr(Tabelle1.Cells(lZeile, 13).Value))
End With
p = p + 1
End If
Next
End If
Next i
End With