'sheet names
Private Const PEPTIDE_NAME As String = "Peptide details"
Private Const PSMS_NAME As String = "PSM details"
Private Const COVERAGE_NAME As String = "Coverage details"

'column names
Private Const PROTEIN_ACCESSION As String = "Accession"
Private Const PSM_ACCESSION = "Protein accessions"

'misc symbols
Private Const MappingSymbol As String = "ü" 'Symbol for mapping psms to samples

Public LastRange As Range

'---------------------------------------- EVENTS ----------------------------------------
'tracks latest range selection
Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Set LastRange = target
End Sub

' tracks clicked hyperlinks
Private Sub Worksheet_FollowHyperlink(ByVal Link As Hyperlink)
    Dim protein As String
    Dim sample As String
    
    If Not Link.Address Like "http?://*" Then
        protein = GetProtein()
        sample = GetSample()
    
        Select Case ActiveCell.Worksheet.Name
            Case COVERAGE_NAME
                Call Filter(PROTEIN_ACCESSION, protein, sample, False)
            Case PEPTIDE_NAME
                Call Filter(PSM_ACCESSION, protein, sample, True)
            Case PSMS_NAME
                Call Filter(PSM_ACCESSION, protein, sample, True)
        End Select
    End If
End Sub


'---------------------------------------- METHODS ----------------------------------------
Private Sub Filter(ByVal proteinColumn As String, protein As String, sample As String, filterSample As Boolean)
    Dim proteinRange As Range
    Dim sampleRange As Range
    Dim uniqueRange As Range
    Dim columnName As String

    With ActiveCell.Worksheet
        'reset former filter
        If (.AutoFilterMode And .FilterMode) Or .FilterMode Then
            .ShowAllData
        End If
        
        Set proteinRange = .UsedRange.Find(proteinColumn, MatchCase:=True)
        Set sampleRange = .UsedRange.Find(sample)
        Set uniqueRange = .UsedRange.Find("Unique")
        columnName = GetColumnName()
        
        'get filter range
        With .Cells(proteinRange.Row, 1)
            'filter by protein
            .AutoFilter Field:=proteinRange.Column, Criteria1:="*" & protein & "*"
            'filter by sample
            If filterSample Then
                .AutoFilter Field:=sampleRange.Column, Criteria1:=MappingSymbol
            End If
            If columnName Like "*Unique*" Then
                .AutoFilter Field:=uniqueRange.Column, Criteria1:=MappingSymbol
            End If
        End With
        
        If Not filterSample Then
            .Columns(sampleRange.Column).SpecialCells(xlCellTypeConstants).End(xlDown).Cells(1, 2).Select
        End If
    End With
    
    'adjust view
    With ActiveWindow
        .ScrollRow = 1
        'focus on sample column
        If Not filterSample Then
            .ScrollColumn = sampleRange.Column
        End If
    End With
End Sub

'Gets the current accession
Private Function GetProtein() As String
    Dim accession As Range
    'find accession header cell
    Set accession = GetAccessionHeader()
    'extract current accession
    GetProtein = LastRange.Worksheet.Cells(LastRange.Row, accession.Column).Value
End Function

'Gets the accession header cell
Private Function GetAccessionHeader() As Range
    Set GetAccessionHeader = LastRange.Worksheet.UsedRange.Find(PROTEIN_ACCESSION)
End Function

'Gets the current sample name (assumes merged sample headers)
Private Function GetSample() As String
    Dim headerRow As Integer
    'extract header row index
    headerRow = GetAccessionHeader().Row
    'get corresponding header cell
    Set sampleRange = LastRange.Worksheet.Cells(headerRow, LastRange.Column).MergeArea
    'first cell of merged range contains sample name
    GetSample = LastRange.Worksheet.Cells(sampleRange.Row, sampleRange.Column).Value
    Exit Function
End Function

Private Function GetColumnName() As String
    Dim headerRow As Integer
    'extract header row index
    headerRow = GetAccessionHeader().Row
    'first cell of merged range contains sample name
    GetColumnName = LastRange.Worksheet.Cells(headerRow + 1, LastRange.Column).Value
    Exit Function
End Function