Clicking a hyperlink in Excel to set autofilter on a different sheet

2
votes

I have an Excel workbook with two sheets, basically a one-to-many setup between the two sheets. The first sheet lists several hundred companies and the second sheet lists the board of directors of the companies. The second sheet has an auto filter so users can see board members for a specific company selected from the filter.

What I am attempting to do is have a user click the cell of the company on the first sheet so the user is then taken to the next sheet with the auto filter already populated with the company selected. That way the user has direct access to the Board Members only for the company selected.

I imagine this would require VBA and was hoping someone could point me in the right direction for creating this code to solve this. Many thanks.

2
vbaexcel
Yep. VBA is needed here. See Worksheet_BeforeDoubleClick event, then tell the users that double-clicking on a cell will perform the action. If you want more specific help, you'll need to post the code you are building and where it's not working. – Scott Holtzman

2 Answers

4
votes

You can accomplish this by doing something this in the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Update Table14 to your table name
    'Update Field to column number of the field you are filtering
    'Update Sheet7 to reference the sheet containing your table
    'Change on to the column number where your click should cause this action
    If ActiveCell.Column = 1 Then
    Sheet7.ListObjects("Table14").Range.AutoFilter Field:=1, Criteria1:=ActiveCell.Value
    'Update Sheet7 to reference the sheet containing your table
    Sheet7.Activate
    End If
End Sub
0
votes

You'll need to open the visual basic editor, right click the company worksheet, view code, paste this in:

Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim CompanyName As String

        If Selection.Count = 1 Then
            If Not Intersect(Target, Range("C1").EntireColumn) Is Nothing Then
                'This code is triggered when any
                'ONE cell in column C is selected
                'Simply change "C1" to "B1" etc etc


                'This MsgBox returns the selected cell
                MsgBox Target.Address

                'You'll probably need to collect some information
                'in this section. You can then use this to affect
                'the filters on sheet 2.
                'Perhaps like this
                CompanyName = Cells(Target.Row, 1).Value
                MsgBox CompanyName

                'This changes to "Sheet2"
                Sheets("Sheet2").Activate

            End If
        End If
    End Sub

Hope that helps and you can make something of it