CODEWITHSUNDEEP
vbaexcel
DROP TABLE users
DROP TABLE users

Reputation: 1955

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

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,

Upvotes: 4

Views: 8489

Answers (4)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

enter image description here

Upvotes: 7

Jon Crowell
Jon Crowell

Reputation: 22340

There are two things you need to fix.

1. Get the value from the hyperlink

In order to get the value from activecell.value, your hyperlinks need to be linking to themselves. (Right-click a hyperlink and select edit hyperlink, select Place in This Document, and type the cell's address in the cell reference.)

A better option is to do what Siddharth suggests.

2. Make sure you select the sheet you want based on the value of the hyperlink

You also need to fix your case statement. If BR is an integer, any value between 3 and 3.5 will evaluate to 3 and select the "Next" sheet instead of the "Calls" sheet.

Upvotes: 2

mkingston
mkingston

Reputation: 2718

The value of your cell is probably stored as text rather than a numeric value. Select the cell, press ctrl+1 then change the type to general, or number. Press OK, then push F2 to change the value in the cell, followed by Enter. The data type is now numeric. Run your code again.

Alternatively, instead of this:

BR = ActiveCell.Value

Use this:

BR = CDbl(ActiveCell.Value)

Upvotes: 1

Jeremy Thompson
Jeremy Thompson

Reputation: 65544

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

Upvotes: 2

Related Questions