Populate Listbox with filtered data

Ghostrider757 · Jul 2nd 2017

I have been trying everything on the internet for the last 2 weeks to find a solution to a seemingly easy problem. Maybe I am looking down the wrong path. Hope someone can chime in with some advice. Heres what I am trying to accomplish.

I have a userform multiselect listbox populated by rowsource in VBA that shows 12 columns per row (It intentionally does not show the 13th column). I would like to filter the table on sheet "ms" to only populate the listbox with all rows that are blank in column 13. Currently I am using autofilter to send the filtered data to a helper sheet and populating the listbox from there. Is it even possible to avoid using the helper sheet? The reason I want to do this is when I select rows in the listbox, it is supposed to add an "X" in column 13. What is happening is the row index on the Original table does not correspond with the filtered data so it puts the "X" on the wrong row if at all. Here is a quick mockup.

[ATTACH=CONFIG]72952[/ATTACH]

I just for the life of me cannot figure out how to accomplish this in each selected row. Can anyone help me?

sktneer · Jul 2nd 2017

Re: Populate Listbox with filtered data

You should upload a sample workbook instead of image to work with.
Also when you select a row in the listbox, how do you identify which row on the sheet is to be updated with a "x" in column 13? Do you have any column with a primary key?

**royUK** · Jul 2nd 2017

Re: Populate Listbox with filtered data

Take a look at this example

Ghostrider757 · Jul 2nd 2017

Re: Populate Listbox with filtered data

Quote from sktneer;795074

You should upload a sample workbook instead of image to work with.
Also when you select a row in the listbox, how do you identify which row on the sheet is to be updated with a "x" in column 13? Do you have any column with a primary key?

I cannot upload the actual workbook due to sensitive data, however I stripped everything out of it and am attaching it. The userform has an error now that I deleted a bunch of stuff but will give you an idea of what I was doing as opposed to how I should be doing it. This is how I select the row to be updated:

Code

Sub markoffentry()
        With Me.ListBox1
            If .ListIndex > 0 Then
                    With Add_MS.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then Sheets("MS").Cells(i + 6, 15) = "X"
        Next i
    End With
            Else
                msgbox "No item selected"
            End If
        End With
End Sub

Display More

forum.ozgrid.com/index.php?attachment/72960/

Ghostrider757 · Jul 2nd 2017

Re: Populate Listbox with filtered data

Quote from royUK;795075

Take a look at this example

Thank you for that. I took a look and its a bit above my head so to speak as I am quite the beginner. I am trying to see how to apply it to my needs, I will keep looking through it in the meantime.

sktneer · Jul 3rd 2017

Re: Populate Listbox with filtered data

Under UserForm Initialize Event code, you may try something like this to populate the listbox to show the rows where column 13 is blank.
See if you can incorporate this approach to your code.

Code

Private Sub UserForm_Initialize()
Dim wsData As Worksheet
Dim lr As Long, n As Long, r As Long, j As Long, i As Long
Dim rng As Range, cell As Range
Dim x()
Set wsData = Sheets("MS")
wsData.AutoFilterMode = False
lr = wsData.Cells(Rows.Count, "C").End(xlUp).Row
With wsData.Range("C5:O5")
    .AutoFilter field:=13, Criteria1:=""
    If wsData.Range("C5:C" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        n = wsData.Range("C6:C" & lr).SpecialCells(xlCellTypeVisible).Cells.Count
        ReDim x(1 To n, 1 To 12)
        Set rng = wsData.Range("C6:C" & lr).SpecialCells(xlCellTypeVisible)
        For Each cell In rng
            r = cell.Row
            i = i + 1
            For j = 3 To 14
                x(i, j - 2) = wsData.Cells(r, j)
            Next j
        Next cell
        Me.ListBox1.ColumnCount = 12
        Me.ListBox1.List = x
    End If
    .AutoFilter
End With
End Sub

Display More

Ghostrider757 · Jul 3rd 2017

Re: Populate Listbox with filtered data

I was able to resolve this, thank you for the assistance!

sktneer · Jul 3rd 2017

Re: Populate Listbox with filtered data

You're welcome!

Populate Listbox with filtered data

Participate now!

Share