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:

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
435
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
435
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
435
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.
 

Forum statistics

Threads
1,078,145
Messages
5,338,507
Members
399,238
Latest member
amuthan10

Some videos you may like

This Week's Hot Topics

Top