Results 1 to 8 of 8

Thread: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    5

    [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.

    example:
    ListBox1 (City, CourseName, ModuleNr, trainer, max. participants)
    (Musterhausen | Course 1 | Module 6 | Mrs. Müller | 23)

    ListBox2 (Name, First name, BirthDay, CustomerNr.)
    (Müller, Max, 01.01.1901, 123456789)

    But this person should only import to ListBox 2 if all data match.
    I have no idea

    I hope somebody can help me.

    Sincerely
    ExploShot

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    5

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    5

    Re: [Excel 2010] Select Row in ListBox1 and entry other data in ListBox2

    Here it is
    Attached Files Attached Files

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    5

    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.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    5

    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
    Many thanks anyway for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width