VBA getting the value of the cell from which a hyper link was clicked

Question

I am trying to send the user to a certian sheet in the workbook based on the value of the hyperlink they click on. "1.01" Is example text in one of the cells that are hyperlinks. I have the event getting triggered when they click with this code, but every time the BR is 0.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer
    BR = ActiveCell.Value
    Select Case BR
    Case Is < 2  'case always gets run because BR is always 0
        Worksheets("Appts").Select
    Case 2 To 3
        Worksheets("Next").Select
    Case 3 To 4
        Worksheets("Calls").Select
    End Select
End Sub

I think my problem is where I am getting the ActiveCell.Value but I haven't been able to figure it out. Any help is appreciated.

Thanks,

Answer 1

Is this what you are trying?

Activecell will always give you the value of the cell that is active. If you want the value of the cell which has the hyperlink then use this.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer

    BR = Target.Range.Value

    Select Case BR
        Case Is < 2: Worksheets("Appts").Select
        Case 2 To 3: Worksheets("Next").Select
        Case 3 To 4: Worksheets("Calls").Select
    End Select
End Sub

EDIT

Since there is lot of comment around using the correct data type. Here is an added info. From your select case it is evident that you are only concerned with values from 1 to 4. In such a case declaring your variable as Integer is just fine. If you are not sure of the user input then introduce a small error handling because then declaring the variable as Integer or Long or Single wouldn't help. For example

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer

    On Error GoTo Whoa

    BR = Target.Range.Value

    Select Case BR
        Case Is < 2: Worksheets("Appts").Select
        Case 2 To 3: Worksheets("Next").Select
        Case 3 To 4: Worksheets("Calls").Select
    End Select

    Exit Sub
Whoa:
    MsgBox "The value of the cell which has the hyperlink doesn't fall in the range 1 to 4"
End Sub

SNAPSHOT

Answer 2

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer
    BR = ActiveCell.Formula
    'or
    BR = ActiveCell.Value2
    Select Case BR
    Case Is < 2  'case always gets run because BR is always 0
        Worksheets("Appts").Select
    Case 2 To 3
        Worksheets("Next").Select
    Case 3 To 4
        Worksheets("Calls").Select
    End Select
End Sub