Automatically setting filters based upon user selection criteria

[JC500]

Hi,

Note: I'm not a VB expert or even a beginner, so I may get the wording wrong so apologies and thanks in advance:

Background:
I have 2 x sheets, called:
• Master: This is a front menu page that all users see that contains an image (4 Pillars) as per the example:

• Each Pillar is associated within a different cell and name i.e.,
B11 = Business, D11 = Capability, F11 = Invocation & G11 = Testing
• Standards: This sheet contains a list of rows that are filtered primarily by the Pillar type:

• Each pillar could contain 1 or more rows. Each row contains additional information i.e. Policy id, Area, policy statement and guidelines

Note: I can associate each pillars in the Master sheet to the "Standards" sheet using the Link feature based upon the following defined names e.g., Business, Capability, Invocation etc. However, this feature does not automatically filter the data so that is shows only the selected pillar the user has selected; in fact it show the current pillar as well as the remaining pillars.

The Ask:
When the user selects one of the Pillars from the Master sheet e.g., D11 = Capability can the "Standards" sheet be automatically filtered so that only the the relevant selection is shown i.e., Capability?

 

[John_w]

• Each Pillar is associated within a different cell and name i.e.,
B11 = Business, D11 = Capability, F11 = Invocation & G11 = Testing

Does that mean the Business pillar image has the name Business in the Name Box? And the Capability pillar image has the name Capability, etc.

If so, this simple solution should fit your setup.

Add the following code in a standard module:

Public Sub Pillar_Click()
    
    If Not IsError(Application.Caller) Then
        With ThisWorkbook.Worksheets("Standards")
            .Activate
            .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Application.Caller
        End With
    Else
        MsgBox "Pillar_Click can only be called by clicking one of the pillars", vbExclamation
    End If
    
End Sub

Next, assign the Pillar_Click macro to each of the 4 pillar images.

Now click one of the pillars and it should activate the Standards sheet and filter column A by the clicked pillar.

NB - it looks like you have merged cells in the Pillars column. Autofilter doesn't display all rows when merged cells are filtered - you must have a value in every cell in column A.