CODEWITHSUNDEEP
vbaexcel
ranopano
ranopano

Reputation: 539

Dynamic hyperlink that filters a table based on the ActiveCell value (VBA)

I'm creating a dynamic hyperlink that will filter a table on another sheet (Sheet15).

My goal is to have the user be able to select a cell on Sheet3 and have the VALUE of this cell be the filter on the other sheet.

Here is my code so far:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Type = msoHyperlinkRange And Target.Range.Address = "$S$15" Then
        Application.ScreenUpdating = False
        Sheet15.Visible = True
        Sheet15.ListObjects("Table17").Range.AutoFilter Field:=19, Criteria1:=ActiveCell.Value
        Sheet15.Activate
        Application.ScreenUpdating = True
    End If
End Sub

However, when I click the hyperlink, the table is not filtered at all, so I gotta be doing something wrong.

Can anyone assist?

UPDATE

Here is updated code.

Cell S17 is now the value that I want to filter the table to:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Type = msoHyperlinkRange And Target.Range.Address = "$S$15" Then
        Application.ScreenUpdating = False
        Sheet15.Visible = True
        Sheet15.ListObjects("Table17").Range.AutoFilter Field:=19, Criteria1:=Sheet3.Range("S17").Value
        Sheet15.Activate
        Application.ScreenUpdating = True
    End If
End Sub

But the issue remains. When I click they hyperlink, I will be brought to this other sheet, but the table is not filtered at all.

Upvotes: 0

Views: 1724

Answers (2)

user3598756
user3598756

Reputation: 29421

sticking to your original plans, and assuming column "A" is the one with cities names, place the following in your worksheet code pane

Option Explicit

Dim lastCell As Range '<--| declare a module scoped range variable to store the last cell selected by the user, if "valid"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$S$15" Then Exit Sub '<-- do nothing if user selected cell with hyperlink
    Set lastCell = Intersect(Target, Columns("A")) '<-- change "Columns("A") to a named range with your cities
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If lastCell Is Nothing Then Exit Sub '<--| no action if lastCell has not been properly set by 'Worksheet_SelectionChange()'

    If Target.Type = msoHyperlinkRange And Target.Range.Address = "$S$15" Then
        Application.ScreenUpdating = False
        Sheet15.Visible = True
        Sheet15.ListObjects("Table17").Range.AutoFilter Field:=19, Criteria1:=lastCell.Value '<--| set the criteria as 'lastCell' value
        Sheet15.Activate
        Application.ScreenUpdating = True
    End If
End Sub

as per comments, you change Columns("A") reference in Worksheet_SelectionChange() to your actual range with cities names (perhaps a named range)

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166306

Note: unless the hyperlink points to itself, ActiveCell.Value will be the value at the link destination: use Target.Range.Value if you want the value from the cell containing the link.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Type = msoHyperlinkRange And Target.Range.Address = "$S$15" Then
        Application.ScreenUpdating = False
        With Sheet15
            .Visible = True
            .ListObjects("Table17").Range.AutoFilter Field:=19, _
                                     Criteria1:=Target.Range.Value
            .Activate
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Upvotes: 1

Related Questions