+ Reply to Thread
Results 1 to 5 of 5

Listbox with unique and dependent items - VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    09-13-2013
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    17

    Listbox with unique and dependent items - VBA

    Hi,

    Sorry if this is a topic well discussed, but I have been working on this basic code to have it doing this:

    I have a Listbox1 in which I was able to populate it with unique items (from columnB in Programmes worksheet) using a collection (that was a good achievement for me!)

    Now, I want to populate a the Listbox2 with items (not repeated) from columnC, BUT only if the correspondent value on columnB is equal to listbox1.value.
    Could you please help me finding a solution for this? I am just a beginner in VBA...

    THANKS!

    <script type="text/javascript">
    <!--
    
    Private Sub CommandButton2_Click()
    
    Dim myList As Collection
    Dim myVal As Variant
    
    Set Programmes = Worksheets("Programmes")
    Set ws = Worksheets("Tables")
    Set myList = New Collection
    
    'Clear listbox
    ws.ListBox2.Clear
    
    'Last used cell in the entry table
    LR = Programmes.Cells(Rows.Count, 1).End(xlUp).Row
    
    
    For X = 5 To LR
        Set myCell = Programmes.Cells(X, 3)
        If Programmes.Cells(X, 2) = ws.ListBox1.Value Then
             myList.Add (myCell.Value), CStr(myCell.Value) 'RUN-TIME ERROR 457 - this key is already associated with an element of this collection
        End If
           
    Next X
    On Error GoTo 0
    
    For Each myVal In myList
    ws.ListBox2.AddItem myVal
    Next myVal
    
    End Sub
    
    //-->
    </script>
    Last edited by limalf; 09-25-2013 at 05:16 PM.

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Listbox with unique and dependent items - VBA

    Hi,

    please edit your post and use CODE tags for your code, you'll find a link how to do that in my signature.

    In order to make your code work you have to place "On Error Resume Next" above the loop where the collection is filled.

    Personally I'd suggest the usage of a dictionary:
    Option Explicit
    
    Private Sub CommandButton2_Click()
    
        Dim myDic As Object
        Dim myVal As Variant
        Dim Programmes As Excel.Worksheet
        Dim ws As Excel.Worksheet
        
        Set Programmes = Worksheets("Programmes")
        Set ws = Worksheets("Tables")
        
        Set myDic = CreateObject("Scripting.Dictionary")
        
        'Clear listbox
        ws.ListBox2.Clear
        
        'Last used cell in the entry table
        LR = Programmes.Cells(Rows.Count, 1).End(xlUp).Row
        
        
        For X = 5 To LR
        
            Set myCell = Programmes.Cells(X, 3)
            If Programmes.Cells(X, 2) = ws.ListBox1.Value Then
                myDic(myCell.Value) = 0
            End If
            
        Next X
        
        For Each myVal In myDic.Keys
            ws.ListBox2.AddItem myVal
        Next myVal
    
    End Sub
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    09-13-2013
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Listbox with unique and dependent items - VBA

    Hi,

    Thank you for your quick help.

    tehneXus, your solution worked perfectly!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: Listbox with unique and dependent items - VBA

    Another one.

    Private Sub UserForm_Initialize()
        
        Dim b As Variant, v As Variant
        
        'Column B values
        With Worksheets("Programmes")
            b = .Range("B5", .Range("B" & Rows.Count).End(xlUp)).Value
        End With
        
        'Populate Listbox1 with unique list from column B
        With CreateObject("Scripting.Dictionary")
            For Each v In b
                .Item(v) = 1
            Next v
            Me.ListBox1.List = .Keys
        End With
        
    End Sub
        
        
    Private Sub CommandButton2_Click()
        
        Dim bc As Variant, i As Long
        
        If Me.ListBox1.ListIndex = -1 Then Exit Sub 'Nothing selected in Listbox1
        
        'Column B:C values
        With Worksheets("Programmes")
            bc = .Range("B5", .Range("C" & Rows.Count).End(xlUp)).Value
        End With
        
        'Populate Listbox2 with unique list from column C of selected item from Listbox1
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(bc, 1)
                If bc(i, 1) = Me.ListBox1.Value Then .Item(bc(i, 2)) = 1
            Next i
            Me.ListBox2.List = .Keys
        End With
        
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  5. #5
    Registered User
    Join Date
    11-22-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Listbox with unique and dependent items - VBA

    Hi,

    The above solution works pretty well when you are selecting only one value of a listbox. But can you help me with the code if one happens to select multiple values in Listbox1?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto select items in a Listbox with items from another Listbox
    By perducci in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-22-2013, 04:45 PM
  2. Copy Selected items from multicolumn, multiselect listbox to another listbox
    By Willigb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2013, 11:27 AM
  3. Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox
    By muneebmansoor in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-28-2013, 02:21 PM
  4. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  5. [SOLVED] Setting up a validation of data listbox to provide the unique items within a range
    By jedale@gmail.com in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-30-2006, 04:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1