Filtered data in Listbox based on Option buttons EXCEL VBA

0

Hello every one,
I don't know about VBA or programming or any other language, I usually try to find solutions on the internet which fulfil my requirements and I use those solutions but this time I didn't find any thus I am posting here. I would appreciate and would thankful if someone please help me out with the code which does my required work.
I have a simple requirement, I have 18 columns sheet with 15 task status, 3 text boxes & 15 frames on form, each frame has 2 options for selection and a list to populate the result.Every task is identified by True or False
Requirements

  1. What I need is same as applying a filter on each column's data based on the value selected in the frame by option button or text box input values.If nothing selected in the frames' options or if the text box is empty then filter will not be applied on that particular column
  2. I do not need all columns to be shown in the list box, a maximum of 5-6 will be enough for verifying & identification


Following are the example scenarios

  • If True is selected in Task5 Frame and all other option buttons are not selected, all text boxes are empty then the list must populate the data rows of all the customers having TRUE in column Task5
  • If False is selected in Task15 Frame, Textbox1 has an input value of Customer1 then the list must populate the data rows of only Customer1 with False in Column Task15



I am using Office 2016, which comes preinstalled with the machine. I do not have access to advance or other Microsoft paid VBA controls.

Answer
Discuss

Discussion

Basically, you just want to fill the customer list based on the True/False selections, correct?
don (rep: 1317) Apr 19, '18 at 10:33 am
yes I want to fill customer list based on the True/False of a prticular Coulmn
saucy_spider Apr 20, '18 at 12:07 am
Add to Discussion

Answers

0

A quick and simple way is to concatenate all of the TRUE/FALSE values and then run a loop through the selected optionbutton controls in the userform and concatenate all of those values and then compare the results and, if the result matches, add the info. from that row into the value to be displayed in the listbox.

Update:

I don't think you understood what I wrote above, put the below code into your UserForm and try it out:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim TFHolder As Variant
Dim OptionButtonValue As Variant
Dim Counter As Long
Dim RowValue() As Variant
Dim RowValue1(), RowValue2(), RowValue3() As Variant

Counter = 0


'Loop through the controls in the form.
For Each FormControl In Me.Controls

    'Check only OptionButtons
    If TypeName(FormControl) = "OptionButton" Then

        ' Check the status of the OptionButton.
        If FormControl.Value = True Then

            OptionButtonValue = FormControl.Caption & OptionButtonValue

        End If

    End If

Next

Set rng = Range("A1:R" & Range("A" & Rows.Count).End(xlUp).row)

For Each row In rng.Rows

    If row.row > 1 Then
        For Each cell In row.Cells
            If cell.Column > 3 Then
               TFHolder = UCase(cell.Value) & TFHolder
            End If
        Next cell

        'Check if row should appear in the list.
        If TFHolder = OptionButtonValue Then

            'make size of array
            ReDim Preserve RowValue1(Counter)
            ReDim Preserve RowValue2(Counter)
            ReDim Preserve RowValue3(Counter)

            'save row info

            'Rows
            RowValue1(Counter) = Cells(row.row, 1).Value
            RowValue2(Counter) = Cells(row.row, 2).Value
            RowValue3(Counter) = Cells(row.row, 3).Value

            'Counter increment
            Counter = Counter + 1

        End If

    End If
    TFHolder = ""

Next row

'Declare the array variable.
'Say how many rows there will be.
ReDim RowValue(Counter - 1, 2) As Variant

'Fill the array for the multi-column list.
For i = 0 To Counter - 1

    RowValue(i, 0) = RowValue1(i)
    RowValue(i, 1) = RowValue2(i)
    RowValue(i, 2) = RowValue3(i)

Next i


'Say how many columns there will be
ListBox1.ColumnCount = 3

'Set Column Widths
ListBox1.ColumnWidths = "20;50;50"


'Put the values into the ComboBox
ListBox1.List = RowValue

End Sub

This returns the results to the listbox in the userform based on the selected True/False values in the form.

Discuss

Discussion

no dear this is not what i want,
I want to fill customer list based on the values in the Task Colum(the column has True or False)
saucy_spider Apr 20, '18 at 12:09 am
Check the updated answer with code and see if that helps.
don (rep: 1317) Apr 21, '18 at 12:33 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login