none
Hyperlink to trigger macro RRS feed

  • Question

  • Hello,

    I am looking to assign several hyperlinks to formula results. An example, a cell would give a result from looking at a table and a hyperlink is created for that cell. When the hyperlink is clicked, the macro runs and filters the table to show just those results. Probably on a different sheet. My knowledge of Excel is very good, minus VBA. Unfortunately. If anyone knows how to accomplish this, I would really appreciate it. Thanks.

    Saturday, January 3, 2015 6:00 PM

Answers

  • Hi Schnyde111,

    Thanks for posting in MSDN forum.

    Based on the description, you want to filter the table after click the hyperlink.

    As far as I know, the Worksheet.FollowHyperlink event Occurs when you click any hyperlink on a worksheet. So we can use this event to catch the click on a hyperlin and we can use AutoFilter to filter a table in the Excel.

    The following example stores the address and filtering criteria for the current filtering and then applies new filters.

    Dim w As Worksheet 
    Dim filterArray() 
    Dim currentFiltRange As String 
     
    Sub ChangeFilters() 
     
    Set w = Worksheets("Crew") 
    With w.AutoFilter 
     currentFiltRange = .Range.Address 
     With .Filters 
     ReDim filterArray(1 To .Count, 1 To 3) 
     For f = 1 To .Count 
     With .Item(f) 
     If .On Then 
     filterArray(f, 1) = .Criteria1 
     If .Operator Then 
     filterArray(f, 2) = .Operator 
     filterArray(f, 3) = .Criteria2 
     End If 
     End If 
     End With 
     Next 
     End With 
    End With 
     
    w.AutoFilterMode = False 
    w.Range("A1").AutoFilter field:=1, Criteria1:="S" 
     
    End Sub
    

    I also suggest that you use recrod a macro to get the similar code to achieve the goal. Keep in mind the recorded macro won't be the best possible code in most cases, but it's does very well for a quick example.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 5, 2015 7:29 AM
    Moderator