none
Filter listbox data RRS feed

  • Question

  • I have a very simple workbook with 2 worksheets - Customers and Projects. In the customers worksheet I have an ID and in the Projects worksheet I have a column Customer ID.

    I have created a form that has 2 combo boxes, the 1st one [CustName] and is populated by the list of customers from the customer worksheet and I have determined the ID using Vlookup. The 2nd one [ProjectName] would be populated from the worksheet Projects, however I would like it to list only the projects relating to the customer - listbox [CustName] based Customer ID matching the ID (much like the relationship in access).

    Sounds easy but stumped.

    Thanks in advance - Ron Godbout


    Ron Godbout

    Tuesday, May 12, 2015 2:15 AM

Answers

  • This is what I use- the code is inserted on the the worksheet vb (right click the worksheet tab- view code.

    The source is on a sheet called List - column 48- shows customers only once

    then in column 66 & 67 I have a table with customers and then projects.you also need to have two combo boxes- called combox1 & 2

    Think tahst all

    D

    Dim rng As Range, c As Range, r As Range, f As Range
    Dim rws As Long, y As Integer
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Address <> "$T$4" Then Exit Sub
        
         rws = Sheets("list").UsedRange.Columns(48).Rows.Count
         
        ComboBox1.clear
        With Worksheets("list")
        Set r = .Range(.Cells(2, 48), .Cells(rws, 48))
       End With
        
        For Each c In r.Cells
            With Worksheets("list")
            y = Application.WorksheetFunction.CountIf(.Range(.Cells(2, 48), .Cells(rws, 48)), c)
          End With
           If y = 1 Then ComboBox1.AddItem c
           
        
        Next c
        With ComboBox1
            .Activate
            .Application.SendKeys ("%{down}")
    
        End With
    
    End Sub
    Private Sub ComboBox1_Change()
    
        rws = Sheets("list").UsedRange.Columns(66).Rows.Count + 2
        Worksheets("input form").ComboBox2.clear
        With Worksheets("list")
        Set r = .Range(.Cells(2, 66), .Cells(rws, 66))
        End With
        
        For Each c In r.Cells
    
            If c = ComboBox1 Then Worksheets("input form").ComboBox2.AddItem c.Offset(0, 1)
        Next c
        With Worksheets("input form").ComboBox2
            .Activate
            .Application.SendKeys ("%{down}")
       End With
    
    End Sub
    Private Sub ComboBox2_Change()
    Range("q2").Value = ComboBox1
    Range("r2").Value = ComboBox2
    Range("h8").Value = Range("s2").Value
    Range("h2").Value = Range("s2").Value
    
    If (Range("s6").Value = "Customer") Then
        Range("c7").Value = Range("q2").Value
            'End
          End If
        
    
        
        'Set f = Range("F16")
        'f = ComboBox1 & " " & ComboBox2
        'f.Select
    End Sub


    • Marked as answer by L.HlModerator Tuesday, May 19, 2015 5:21 AM
    Tuesday, May 12, 2015 2:10 PM