Help to modify code Worksheet_Change(ByVal Target As Range) to ByVal Target As Object

[SSF1590]

Hello,

I have this code that filters data by the selection in cell 8 (data variation). Can I please have your help to modify ByVal Target As Range to ByVal Target As Object so it filters by the selection made in combobox1?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$8" Then
    If Range("C8").Formula = "All" Then
        Range("A13").AutoFilter
    Else
        Range("A13").AutoFilter Field:=13, Criteria1:=Range("C8")
    End If
End If
End Sub

 

[Paul Ked]

Why not put the value of the ComboBox into a cell and use that value?

 

[SSF1590]

I am sorry for my lack of knowledge in VBA. I pretty much have basic knowledge. Can you please explain me how?
I actually tried to place the value of the combobox in a cell but by using a formula. VBA does not read it of course, so I am kind of lost.

 

[Paul Ked]

If its a Form Control combo on the sheet, then right click > Format Control > Control and enter the info there.

 

[SSF1590]

Sorry I think I got you wrong. You mean to use data validation instead of a combobox and leave the code as it is. The current code I have work with the value on cell 8 using data validation.

 

[Paul Ked]

Now I am confused. If it's working with Data Validation, why change it to Combobox1?

 

[SSF1590]

Yes, it is working with data validation. The reason k would like to change is because with data validation you don’t know you have a drop down list until you click on the cell. With a ComboBox it’s pretty clear.

 

[Paul Ked]

Here's an example of a Combobox. https://1drv.ms/x/s!Av8oW8_xzD3ErFBYUIuudaLWH8hy?e=dnkec7

There is no VBA involved.

Here's a link to a quick video tutorial https://www.youtube.com/watch?v=J_ORTUwmhuo