Hyperlink and autofilter table based on changing cell value (VBA)

Question

What I am trying to do is create a hyperlink on Sheet1 that autofilters a table on Sheet2. This table should filter based on a certain cell value in Sheet1. The cell value changes dynamically based on slicer selection.

So for instance,

if the cell value on Sheet1 displays "Donald Trump", then the table on Sheet2 should filter to display only "Donald Trump" in column 13.
If the cell value on Sheet2 displays "Hilary Clinton", then the table on Sheet2 should filter to display only "Hilary Clinton" in column 13. And so on and so forth...

I'm very inexperienced with VBA so I was hoping for some assistance.

Here's what I got so far, but nothing happens when I try and click the hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveSheet.Range("S18").Select = 1 Then
  'This is the cell with the hyerlink in it.
Worksheets("Table of Presidents").ListObjects("Table17").Range.AutoFilter Field:=13, Criteria1:=Worksheets("Dashboard").Range("S21").Value
 'This is the table that needs to be filtered.
 'The criteria1 part is the field that dynamically changes
Worksheets("Table of Presidents").Activate
End If
End Sub

z32a7ul z32a7ul · Accepted Answer · 2016-11-17T23:05:20

Hillary Clinton is spelled with double l. (But this is not what causes the problem)
Worksheet_BeforeDoubleClick is an Event Handler that is called whenever you double click somewhere, you don't need this but Worksheet_FollowHyperlink.
ActiveSheet.Range("S18").Select means something like click on Cell S18; it has no return value, so you cannot compare it with 1.
You don't really need a macro for this. I mean using a macro has some drawbacks: requirements on fileformat, etc. This task can be done by setting the filter based on formulas (using a hidden column or conditional formatting) and adding a link which simply targets a cell on your Table of Presidents sheet.

Update

Re 2-3:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Type = msoHyperlinkRange And Target.Range.Address = "$S$18" Then
        Worksheets("Table of Presidents").ListObjects("Table17").Range.AutoFilter Field:=13, Criteria1:=Worksheets("Dashboard").Range("S21").Value
        Worksheets("Table of Presidents").Activate
    End If
End Sub

Hyperlink and autofilter table based on changing cell value (VBA)

1 Answers