PDA

View Full Version : VBA help in creating a dependent and source views



himajam
07-31-2019, 10:10 AM
Hi Excel Experts,
I'm looking for vba code for my below excel data. I couldn't figure out how to do it. Hope someone could help me
I have Product and Categories data (2 columns). For each Product, it should show me its corresponding Categories and on click of category, it should show me corresponding products.

Eg data:
Product Category
P1 C1
P1 C2
P2 C1
P3 C1
P3 C2
P3 C3

Output data:
P1 C1 If I click on C1, it should show me P1,P2,P3
---C2 If I click on C2, it should show me P1,P3

P2 C1 If I click on C1, it should show me P1,P2,P3

P3 C1 If I click on C1, it should show me P1,P2,P3
----C2 If I click on C2, it should show me P1,P3
----C3 If I click on C3, it should show me P1,P2,P3

p45cal
08-01-2019, 04:51 AM
There is some built-in functionality for this; right-click on a cell and choose Filter|Filter by selected cell's value.
You could automate it a bit with this in the sheet's code-module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
If (Me.AutoFilterMode And Me.FilterMode) Or Me.FilterMode Then Me.ShowAllData
Range("A1").CurrentRegion.Resize(, 2).AutoFilter Field:=Target.Column, Criteria1:=Target.Value
End If
End Sub

himajam
08-01-2019, 03:45 PM
Hi p45cal (VBAX Guru) :hi: , Thanks a lot... It worked like a charm... Kudos to you... You have solved it just like that (minimal code).. Awesome