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

 

[baleshst]

Hi Peter,

To begin with, thanks a lot for your help.

I made the changes you asked me to do, and it works fine, when the Union checkbox is ticked along with for example EO and YPO (it shows all line items with 'x' in both the columns ).

However, for example, if I uncheck Union but the checks on EO and YPO is still there. The unique list of EO and YPO for 'x' is not showing up (this was the original solution you had given).

So there are 2 aspects to the above
1) When i only select checkboxes without checking the Union Checkbox - it should show me the unique list between the columns (which was original solution you had given) - Currently not covered in your VBA code
2) When select checkboxes along with the Union Checkbox - It should show all 'x' in all columns selected. - Solved by you in the above vba code.

Apologies if I dint make this clear earlier.

thanks

 

[Peter_SSs]

If I have understood correctly, then I believe that it is working for me. using your example above:
My original, unfiltered sheet

EO, YPO and Union:

Now remove the Union checkmark:

Isn't that the result that I should be getting for my sample data?

 

[baleshst]

If I have understood correctly, then I believe that it is working for me. using your example above:
My original, unfiltered sheet
View attachment 79691

EO, YPO and Union:
View attachment 79692

Now remove the Union checkmark:
View attachment 79693

Isn't that the result that I should be getting for my sample data?

Yes that is correct.

Not sure what I am doing wrong.

 

[Peter_SSs]

Not sure what I am doing wrong.

• Double-check all the names of your check boxes and that they match the 'click' code procedure names in the worksheet module.
• Check that each of the check boxes is linked to the relevant worksheet cell. That is, do the correct cells change TRUE/FALSE as you check/uncheck the check box?
• Double-check the formulas in G1 and the Union column. G1 should be counting how many TRUE values in B1:F1. Union formula should be counting how many 'x' values in the row for which the row 1 value is TRUE

If still a problem, put a Breakpoint on each CBFilter line ..

.. then as you check/uncheck a checkbox, ensure that the code stops at one of these Breakpoints then step through the rest of the code steadily with F8 and see what is happening on the worksheet. It might give a clues as to what is going wrong.

If still a problem, can you upload a sample file (any sensitive data disguised or removed) to DropBox or OneDrive or Google drive etc and provide a public shared link here?

 

[baleshst]

• Double-check all the names of your check boxes and that they match the 'click' code procedure names in the worksheet module.
• Check that each of the check boxes is linked to the relevant worksheet cell. That is, do the correct cells change TRUE/FALSE as you check/uncheck the check box?
• Double-check the formulas in G1 and the Union column. G1 should be counting how many TRUE values in B1:F1. Union formula should be counting how many 'x' values in the row for which the row 1 value is TRUE

If still a problem, put a Breakpoint on each CBFilter line ..
View attachment 79701
.. then as you check/uncheck a checkbox, ensure that the code stops at one of these Breakpoints then step through the rest of the code steadily with F8 and see what is happening on the worksheet. It might give a clues as to what is going wrong.

If still a problem, can you upload a sample file (any sensitive data disguised or removed) to DropBox or OneDrive or Google drive etc and provide a public shared link here?

Thanks a lot Peter, I was goofing up somewhere in the codes.

Finally done, really appreciate your patience.

A friendly advise, you should start a youtube channel to train in vba, I would be your subscriber number 1

thanks a ton!!!
Balesh

 

[Peter_SSs]

You're welcome. Glad we got there in the end.