VBA code to give column number based on cell value

[baleshst]

Hi,

I am using the following code, on a checkbox. However i have manually enter column number (Field:=7) below . I would request someone to adjust the below code such that it would give the column number based on the cell content. ie, find a specific word in the worksheet and provide its column number.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:= _
"<>"
Else
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7

End If

End Sub

 

[Legacy 143009]

@baleshst This will filter "x"es:

Private Sub CheckBox1_Click()
  Dim r As Range
  Dim tb As ListObject
  Dim i As Integer
  Dim ws As Worksheet
  Dim cb As CheckBox

  Set ws = ActiveSheet
  Set tb = ws.ListObjects("Table1")
  Set r = tb.Range

  For Each cb In ws.CheckBoxes
    i = tb.ListColumns(cb.Caption).Index
    If cb.Value = 1 Then
      r.AutoFilter Field:=i, Criteria1:="x"
    Else
      r.AutoFilter Field:=i
    End If
  Next
End Sub

@Alex Blakenburg I have a question for you. I had difficulty getting checkbox caption by this method: CheckBox1.Caption
I thought it was a language issue and I also tried in my language: OnayKutusu1.Caption
It kept throwing Object error. But it works while looping through controls. Am I doing something wrong?

 

[baleshst]

@baleshst This will filter "x"es:

Had already tried it, its not working.
To be sure I am doing it correct.
I right clicked on the EO check box, went to view code and copy pasted your code.
Just to add these are active control check boxes (if it makes any difference)

 

[Legacy 143009]

Please note that this code will throw an error if you have no table headers with the checkbox caption:

Private Sub CheckBox1_Click()
  Dim r As Range
  Dim tb As ListObject
  Dim i As Integer
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set tb = ws.ListObjects("Table1")
  Set r = tb.Range

  i = tb.ListColumns(CheckBox1.Caption).Index
  If CheckBox1.Value Then
    r.AutoFilter Field:=i, Criteria1:="x"
  Else
    r.AutoFilter Field:=i
  End If
End Sub

 

[Peter_SSs]

What about something like this (I am assuming that the checkboxes are ActiveX checkboxes)

Private Sub CheckBox1_Click()
  CBFilter CheckBox1.Caption, CheckBox1.Value
End Sub

Private Sub CheckBox2_Click()
  CBFilter CheckBox2.Caption, CheckBox2.Value
End Sub

'etc

Then this to use will all of the above.

Sub CBFilter(s As String, TF As Boolean)
  With ActiveSheet.ListObjects("Table1")
    If TF Then
     .Range.AutoFilter Field:=.ListColumns(s).Index, Criteria1:="<>"
    Else
      .Range.AutoFilter Field:=.ListColumns(s).Index
    End If
  End With
End Sub

 

[baleshst]

What about something like this (I am assuming that the checkboxes are ActiveX checkboxes)

Private Sub CheckBox1_Click()
  CBFilter CheckBox1.Caption, CheckBox1.Value
End Sub

Private Sub CheckBox2_Click()
  CBFilter CheckBox2.Caption, CheckBox2.Value
End Sub

'etc

Then this to use will all of the above.

Sub CBFilter(s As String, TF As Boolean)
  With ActiveSheet.ListObjects("Table1")
    If TF Then
     .Range.AutoFilter Field:=.ListColumns(s).Index, Criteria1:="<>"
    Else
      .Range.AutoFilter Field:=.ListColumns(s).Index
    End If
  End With
End Sub

@Peter_SSs
Superb!!! Works like a charm

@Flashbond
Appreciate your help

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.

 

[baleshst]

You're welcome. Glad it worked for you. Thanks for the follow-up.

Hey Peter,

Just a follow up on the query you had helped me with. There is some additions required in the report. Would be great if you could help me out.

See jpeg for reference.

I have prepared the entire file based on the codes you had helped me with. as seen below (additionally, I have put the union checkbox will explain below)

When i click more than 1 checkbox it filters out the unique line items in those 2 columns and solves the first problem of finding the unique line items in between the 2 columns. See example below

The addition required along with the above solution is as follows.
I have put a Union button, when the user clicks the Union button and selects the EO and YPO criteria as seen in our example, it should show all "x" line items in both columns and not show just the unique items. Please note user may tick the Union checkbox before or after selecting the EO and YPO checkboxes.

So in the above example the output should have been as below, in EO and YPO all columns should be visible as 'x' is there in one of the each column for all line items

I hope, i was able to explain the problem

thanks

 

[Peter_SSs]

What should show if the Union box is ticked while none of the other boxes are ticked? That is, Union is clicked in the first image above.

 

[baleshst]

What should show if the Union box is ticked while none of the other boxes are ticked? That is, Union is clicked in the first image above.

It should not filter anything, just show the entire table.

 

[Peter_SSs]

Thanks. This is now a considerably more complex task but try this.
I have named all of my check boxes according to their captions. So, in the sheet module I now have

Private Sub cbEO_Click()
  CBFilter
End Sub

Private Sub cbYPO_Click()
  CBFilter
End Sub

Private Sub cbEOYPO_Click()
  CBFilter
End Sub

Private Sub cbYPO_Gold_Click()
  CBFilter
End Sub

Private Sub cbYPO_Mumbai_Connect_Click()
  CBFilter
End Sub

Private Sub cbUnion_Click()
  CBFilter
End Sub

Also, in design mode I have linked each checkbox to a cell. For the left hand 5 boxes I linked each to the cell in row 1 above the relevant column. So cbEO is linked to B1, cbYPO to C1 etc.
cbUnion I linked to F2.
In cell G1 I put the formula

=SUM(--B1:F1)

I added a new column headed "Union" to the table and populated it with the formula

=COUNTIFS(B$1:F$1,TRUE,Table1[@[EO]:[YPO Mumbai Connect]],"<>")

So with nothing checked, my sheet looks like this

All those linked and formula cells at the top could be formatted with white text or moved elsewhere and hidden if you want.
The new 'Union' column could also be hidden.

In a standard module I have this procedure.

Sub CBFilter()
  Dim c As Long
 
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects("Table1")
    .AutoFilter.ShowAllData
    If Range("F2").Value And Range("G1").Value > 0 Then
      .Range.AutoFilter Field:=.ListColumns("Union").Index, Criteria1:=">0"
    Else
      For c = 2 To 6
        If Cells(1, c).Value Then
         .Range.AutoFilter Field:=c, Criteria1:="<>"
        Else
          .Range.AutoFilter Field:=c
        End If
      Next c
    End If
  End With
  Application.ScreenUpdating = True
End Sub