Search code examples
excelvbanamed-ranges

How to check if hyperlink target's name contains a specific string?


I have cells in a workbook that link to cells that are named (individually). The cell names all start with "Filter", with some string after that (e.g. FilterSubcategory1").

If the user clicks one of the links, it takes them to the linked cell and then filters another sheet based on where the target was (using .Address at the moment, all works fine). As they all have the same starting string in their name, is it possible to filter if the target name starts with "Filter" instead? This would make my code far shorter rather than listing all relevant named ranges.

It would look something like this (for illustrative purposes, not my full or optimised code):

If Target.Range.Name = "Filter" & "*" Then
'rest of code, do the filtering
End If

or:

If InStr(Target.Range.Name, "Filter") > 0 Then
'rest of code, do the filtering
End If

Solution

  • So, after trying other users' solutions (very helpful in the lead up to the solution), I managed to solve it in a different way. Some hyperlinks linked to themselves (in fact, all the ones that filter do), some linked to merged cells too, so this was the method I used for any cells that linked to themselves:

    FltrStr = "='" & ActiveSheet.Name & "'!" & ActiveCell.Address     'builds up the cell address from scratch, allowing for a merged cell being selected
    Set FltrRng = Range(FltrStr)     'converts the string into the actual range needed, important for the next line
    FltrName = FltrRng.Name.Name     'gets the name of the range, reporting the given name if it exists, lookup .name.name if you are unsure why two .names are needed
    

    I can then use this to check if the target range has a name which starts with whatever I want, here 'Filter':

    If InStr(FltrName, "Filter") > 0 Then
        'rest of code, do the filtering
    End If
    

    Probably not the prettiest/most efficient way to do it, but it works. May be worth defining the types of the variables (FltrStr, FltrRng, FltrName) explicitly at the start of the sub to avoid any type mismatch errors, but I haven't defined them at the moment and they are working fine. (Bad practice, I know!)