VBA Hyperlink with Autofilter

[Marklaus]

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Column <> 3 Then Exit Sub ' Check if the clicked hyperlink is in column C
    Dim last As Long
    Dim ws As Worksheet
    Set ws = Sheets("CRKT Progress")
    last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If ws.AutoFilterMode Then ws.AutoFilterMode = False ' Clear existing filters
    ws.Range("A2:G" & last).AutoFilter Field:=2, Criteria1:=Target.Range.Offset(0, -1).Value
    Application.Goto ws.Range("A2")
End Sub

This is my code so far.
The issue is when it executes it doesnt get the value of the cell in the hyperlink and use it to filter the other sheet.

 

[vletm]

Marklaus​

Have You checked - what is Your used Criteria1's value?
Have You tested Criteria1:= Target.Value?

 

[Marklaus]

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim clickedCell As Range
    Set clickedCell = Target.Parent
    
    If clickedCell.Column <> 3 Then Exit Sub
    
    Dim ws As Worksheet
    Set ws = Sheets("CRKT Progress")
    
    If ws Is Nothing Then Exit Sub
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    If ws.AutoFilterMode Then ws.AutoFilterMode = False ' Clear existing filters
    
    ws.Range("A2:G" & lastRow).AutoFilter Field:=2, Criteria1:=clickedCell.Value
    
    Application.Goto ws.Range("A2") ' Move cursor to A2 after filtering
End Sub

I tried but it doesnt work. I revised the code not it looks that it is iterating the search with all the values on column C and stops with the last value. It supposed to just filter using the value of the clicked cell with hyperlink.

 

[vletm]

Marklaus​

Did You notice that I asked two questions?
... You seems to skip the 1st one
... with the 2nd one, You've used something different.
... ... did You even check - what is its value?

 

[Marklaus]

Using the first code It filtered but none of the supposed value was shown so it showed no result.
I tried using Criteria1=Target.Value runtime error 438 shows object does not support this property or method.

 

[vletm]

Value of Criteria1? ... Value of Criteria1? ... Value of Criteria1?
Do You know any valid value for Criteria1 which could give results?
If You know ... then You could test with it to verify that Your AutoFilter works.
Above means eg Criteria1:= Your_valid_value_for_Criteria1
If above works ...
Find possible variable eg with Watch-window.
Add there Target
... and add Break to Your If-line
'Run' Your code and find out which variable gives needed Criteria1
Are You sure that Your code runs even until lastrow-line? ( two Exits )

 

[Marc L]

it doesnt get the value of the cell in the hyperlink

'Cause the code very does not read the hyperlink cell, bad Offset use or bad explanation …​

​

 

[Marklaus]

'Cause the code very does not read the hyperlink cell, bad Offset use or bad explanation …​

​

Okay now i get it. I tried one of the valid values as criteria and it worked. Now the only issue is how i will make it get the value of cell with hyperlink as its criteria for filtering. Thank you!

 

[Marc L]

Assuming the hyperlink destination cell is in the same worksheet than the range to filter :​

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        If Target.Range.Column <> 3 Then Exit Sub
   With ActiveSheet.[A1].CurrentRegion.Rows
       .Item(2).AutoFilter
       .Item("2:" & .Count).AutoFilter 2, ActiveCell
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​