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

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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?

Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
Why not put the value of the ComboBox into a cell and use that value?
 

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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

Active Member
Joined
Jun 4, 2015
Messages
442
If its a Form Control combo on the sheet, then right click > Format Control > Control and enter the info there.
 

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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

Active Member
Joined
Jun 4, 2015
Messages
442
Now I am confused. If it's working with Data Validation, why change it to Combobox1?
 
Last edited:

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,039
Messages
5,448,051
Members
405,480
Latest member
bell282

This Week's Hot Topics

Top