none
Populate Listbox if cell does not contain certain data RRS feed

  • Question

  • I need to fill a listbox on a userform from a spreadsheet.  I can do this with no problems.

    I have a spreadsheet with multiple columns. I am using Column A as my listbox data.  However, I do not want all of the items to populate.  I would like to know how to search cells in specific columns for certain key words and if the words are found, not populate Column A of that row in to the listbox.

    Does this make sense? 

    Wednesday, June 20, 2012 5:45 PM

All replies

  • Leave the RowSource of the list box blank, and populate it in the UserForm_Initialize event.

    Here is an example. If column C contains "not" or if column "E" contains "exclude", the row is skipped.

    Private Sub UserForm_Initialize()
        Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets("Data")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("C" & r) <> "not" And wsh.Range("E" & r) <> "exclude" Then
                Me.ListBox1.AddItem wsh.Range("A" & r)
            End If
        Next r
    End Sub


    Regards, Hans Vogelaar

    Wednesday, June 20, 2012 6:20 PM
  • Thanks for the help, but it doesn't seem to be working.  It pulls all of the rows in Column A into the listbox.

    Wednesday, June 20, 2012 7:34 PM
  • The code that I posted was obviously only an example. You'll have to modify it to fit your requirements.

    If you need more help, please provide detailed information about the conditions under which a row should or should not be included.


    Regards, Hans Vogelaar

    Wednesday, June 20, 2012 7:53 PM
  • Private Sub UserForm_Initialize()
        Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets("Data")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("B" & r) <> "NA" Or wsh.Range("C" & r) <> "NA" Then
         
                Me.ListBox1.AddItem wsh.Range("A" & r)
            End If
        Next r
    End Sub
    
    
    
    Private Sub UserForm_Initialize()
    
        Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets("MAGAZINE DATA")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("I" & r) <> "NO DEFECTS NOTED" Or wsh.Range("K" & r) <> "NA" Then
                Me.ListBox1.AddItem wsh.Range("A" & r)
            End If
        Next r
        
        
    
    End Sub
    

    The first bit of code works, but the second does not.  I have searched and searched to figure out what the problem is. Yes, I know that the worksheet name is different, the columns and the criteria.   I have these in two different forms.
    Thursday, June 21, 2012 12:10 PM
  • Text comparison in VBA is case sensitive by default. If a cell contains "No Defects Noted", it is seen as different from "NO DEFECTS NOTED".

    Does it help if you change the line

            If wsh.Range("I" & r) <> "NO DEFECTS NOTED" Or wsh.Range("K" & r) <> "NA" Then
    to

            If UCase(wsh.Range("I" & r)) <> "NO DEFECTS NOTED" Or _
                UCase(wsh.Range("K" & r)) <> "NA" Then


    Regards, Hans Vogelaar


    Thursday, June 21, 2012 12:25 PM
  • Nothing changed.

    Thursday, June 21, 2012 12:55 PM
  • Could you create a copy of the workbook without sensitive data, and upload the copy to one of the websites that let you share a file, such as Windows Live SkyDrive (https://skydrive.live.com) or DropBox (http://www.dropbox.com)? Make sure that you share the uploaded file with everyone, then post a link to the file here.

    Regards, Hans Vogelaar

    Thursday, June 21, 2012 1:56 PM
  • Leave the RowSource of the list box blank, and populate it in the UserForm_Initialize event.

    Here is an example. If column C contains "not" or if column "E" contains "exclude", the row is skipped.

    Private Sub UserForm_Initialize()
        Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets("Data")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("C" & r) <> "not" And wsh.Range("E" & r) <> "exclude" Then
                Me.ListBox1.AddItem wsh.Range("A" & r)
            End If
        Next r
    End Sub


    Regards, Hans Vogelaar

    Hi, this was usefull to me. This works on a single column ListBox.

    What if I have a multiple column ListBox? Say I have Data on columns A and B of a Excel worksheet and I want to display both of them on a List Box with the same criteria you used.

    Thursday, June 21, 2012 3:19 PM
  • @Seaman7:

    You can use the Column property of the list box to set the value of additional columns:

    Private Sub UserForm_Initialize()
        Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets("Data")
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("C" & r) <> "not" And wsh.Range("E" & r) <> "exclude" Then
                With Me.ListBox1
                    .AddItem wsh.Range("A" & r)
                    .Column(1, .ListCount - 1) = wsh.Range("B" & r)
                End With
            End If
        Next r
    End Sub


    Regards, Hans Vogelaar

    Thursday, June 21, 2012 3:27 PM
  • It's working...

    Thanks Hans :-)

    Thursday, June 21, 2012 6:16 PM
  • For some reason, I can not open either of those sites.

    I have been trying to get this to work, and now it will show the first four rows in column A no matter what is in K or I.

    Dim r As Long
        Dim m As Long
        Dim wsh As Worksheet
        Set wsh = Worksheets(8)
        m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
        For r = 2 To m
            If wsh.Range("K" & r) <> "NA" Or wsh.Range("I" & r) <> "NA" Then
                 
                Me.ListBox1.AddItem wsh.Range("A" & r)
            End If
        Next r

    
    Monday, June 25, 2012 1:11 PM
  • You might register at www.eileenslounge.com (free) and post a question in the Excel forum there. You can attach files up to 250 KB there (zipped if necessary).

    Regards, Hans Vogelaar


    Monday, June 25, 2012 2:15 PM